尝试使用NPOI创建一个新的.xlsx文件并写入它 [英] Trying to create a new .xlsx file using NPOI and write to it
问题描述
编辑:
我正在尝试编写一个小型控制台应用程序,它将从excel电子表格中读取,解析这些行,并将字段数据写入新的excel文件。我正在使用.NET和NPOI库。我终于在Apache挖掘之后,在apache站点上发现了POI的Java文档。这是我的更新的代码与新的错误。
I'm trying to write a small console application that will read in lines from an excel spreadsheet, parse the lines and write the fielded data to a new excel file. I'm using .NET and the NPOI library. I finally after much digging found Java documentation for POI on the apache site. Here is my updated code with new errors.
这实际上创建一个可读文件,除了它只写文本到第三列。
This actually creates a readable file except it only writes text to the third column.
public static void TransferXLToTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("City", typeof(string));
dt.Columns.Add("State", typeof(string));
dt.Columns.Add("Zip", typeof(string));
using (FileStream stream = new FileStream(OpenFile(), FileMode.Open, FileAccess.Read))
{
IWorkbook wb = new XSSFWorkbook(stream);
ISheet sheet = wb.GetSheet("Sheet1");
string holder;
int i = 0;
do
{
DataRow dr = dt.NewRow();
IRow row = sheet.GetRow(i);
try
{
holder = row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
}
catch (Exception)
{
break;
}
string city = holder.Substring(0, holder.IndexOf(','));
string state = holder.Substring(holder.IndexOf(',') + 2, 2);
string zip = holder.Substring(holder.IndexOf(',') + 5, 5);
dr[0] = city;
dr[1] = state;
dr[2] = zip;
dt.Rows.Add(dr);
i++;
} while (!String.IsNullOrEmpty(holder));
}
using (FileStream stream = new FileStream(@"C:\Working\FieldedAddresses.xlsx", FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
ICreationHelper cH = wb.GetCreationHelper();
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < 3; j++)
{
IRow row = sheet.CreateRow(i);
ICell cell = row.CreateCell(j);
cell.SetCellValue(cH.CreateRichTextString(dt.Rows[i].ItemArray[j].ToString()));
}
}
wb.Write(stream);
}
}
推荐答案
弄清楚出了什么问题。这实际上是一个非常简单的错误,当我需要在外部循环中创建行时,我正在内部最多的for循环中创建一个新行。希望答案中的工作代码对于需要NPOI起点的任何人都是有用的。
I figured out what was wrong. It was actually a very simple mistake, I was creating a new row in the inner most for loop when I needed to create the row in the outer loop. Hopefully the working code in the answer will be useful for anyone who needs a starting point with NPOI.
这个网站对于从POI或NPOI开始的任何人来说都是一个很好的资源。
This website is a great resource for anyone starting out with POI or NPOI.
http:// poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook
以下是工作代码。
public static void TransferXLToTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("City", typeof(string));
dt.Columns.Add("State", typeof(string));
dt.Columns.Add("Zip", typeof(string));
using (FileStream stream = new FileStream(OpenFile(), FileMode.Open, FileAccess.Read))
{
IWorkbook wb = new XSSFWorkbook(stream);
ISheet sheet = wb.GetSheet("Sheet1");
string holder;
int i = 0;
do
{
DataRow dr = dt.NewRow();
IRow row = sheet.GetRow(i);
try
{
holder = row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
}
catch (Exception)
{
break;
}
string city = holder.Substring(0, holder.IndexOf(','));
string state = holder.Substring(holder.IndexOf(',') + 2, 2);
string zip = holder.Substring(holder.IndexOf(',') + 5, 5);
dr[0] = city;
dr[1] = state;
dr[2] = zip;
dt.Rows.Add(dr);
i++;
} while (!String.IsNullOrEmpty(holder));
}
using (FileStream stream = new FileStream(@"C:\Working\FieldedAddresses.xlsx", FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
ICreationHelper cH = wb.GetCreationHelper();
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < 3; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(cH.CreateRichTextString(dt.Rows[i].ItemArray[j].ToString()));
}
}
wb.Write(stream);
}
}
这篇关于尝试使用NPOI创建一个新的.xlsx文件并写入它的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!