刚学了NPOI,从网上找到个【读取EXCEL文件到DataTable】的源码,试了下,能读取EXCEL文件的第一行(我指定第一行为标题行),而后的数据行就读取不了,请各位大大指点一下,谢谢!ASPX页面只有一个Button(单击事件)、一个GridView(允许自动生成列),代码不帖了。
EXCEL文件路径是直接指定的,如果有哪位好心的大大想帮我测试的,直接在D盘根目录下建立个“abc.xls”的文件,第一个sheet名叫“aaa”,内容随意(多行)。贴出后台代码:
这里是按钮点击事件代码 //导入EXCEL文件按钮
protected void Button2_Click(object sender, EventArgs e)
{
String savePath = @"D:abc.xls";//EXCEL文件路径
if (System.IO.File.Exists(savePath))
{
Page.Response.Write("success!");
DataTable dt = this.ImportDataTableFromExcel(savePath, "aaa", 0);//第二个参数是该EXCEL文件的sheet名
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
}
else
{
Page.Response.Write("error!");
}
}
下面是EXCEL--DataTable实现代码,有1个重载。
/// <summary>
/// 读取EXCEL文件名、sheet,标题行索引
/// </summary>
/// <param name="excelFilePath">文件名</param>
/// <param name="sheetName">sheet名</param>
/// <param name="headerRowIndex">标题行索引</param>
/// <returns>返回DataTable</returns>
public DataTable ImportDataTableFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
{
using (System.IO.FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ImportDataTableFromExcel(stream, sheetName, headerRowIndex);//调用重载的方法
}
}
重载,这里是转换功能方法实现的代码。 /// <summary>
/// 从EXCEL文件导入到DataTable
/// </summary>
/// <param name="excelFileStream">EXCEL文件流</param>
/// <param name="sheetName">sheet名</param>
/// <param name="headerRowIndex">标题行索引</param>
/// <returns>返回DataTable</returns>
protected DataTable ImportDataTableFromExcel(System.IO.Stream excelFileStream, string sheetName, int headerRowIndex)
{
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelFileStream);//创建工作簿对象
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheet(sheetName);//获取参数指定的sheet
DataTable table = new DataTable();
NPOI.SS.UserModel.Row headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
//读取并生成标题行,这里能成功执行
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
//逐个读取单元格,这里就不能正确读取到
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
EXCEL文件路径是直接指定的,如果有哪位好心的大大想帮我测试的,直接在D盘根目录下建立个“abc.xls”的文件,第一个sheet名叫“aaa”,内容随意(多行)。贴出后台代码:
这里是按钮点击事件代码 //导入EXCEL文件按钮
protected void Button2_Click(object sender, EventArgs e)
{
String savePath = @"D:abc.xls";//EXCEL文件路径
if (System.IO.File.Exists(savePath))
{
Page.Response.Write("success!");
DataTable dt = this.ImportDataTableFromExcel(savePath, "aaa", 0);//第二个参数是该EXCEL文件的sheet名
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
}
else
{
Page.Response.Write("error!");
}
}
下面是EXCEL--DataTable实现代码,有1个重载。
/// <summary>
/// 读取EXCEL文件名、sheet,标题行索引
/// </summary>
/// <param name="excelFilePath">文件名</param>
/// <param name="sheetName">sheet名</param>
/// <param name="headerRowIndex">标题行索引</param>
/// <returns>返回DataTable</returns>
public DataTable ImportDataTableFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
{
using (System.IO.FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ImportDataTableFromExcel(stream, sheetName, headerRowIndex);//调用重载的方法
}
}
重载,这里是转换功能方法实现的代码。 /// <summary>
/// 从EXCEL文件导入到DataTable
/// </summary>
/// <param name="excelFileStream">EXCEL文件流</param>
/// <param name="sheetName">sheet名</param>
/// <param name="headerRowIndex">标题行索引</param>
/// <returns>返回DataTable</returns>
protected DataTable ImportDataTableFromExcel(System.IO.Stream excelFileStream, string sheetName, int headerRowIndex)
{
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelFileStream);//创建工作簿对象
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheet(sheetName);//获取参数指定的sheet
DataTable table = new DataTable();
NPOI.SS.UserModel.Row headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
//读取并生成标题行,这里能成功执行
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
//逐个读取单元格,这里就不能正确读取到
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
{
InitializeWorkbook(); //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
//So we insert three sheet just like what Excel does
Sheet sheet1 = hssfworkbook.CreateSheet("Multiple Table"); //create horizontal 1-9
for (int i = 1; i <= 9; i++)
{
sheet1.CreateRow(0).CreateCell(i).SetCellValue(i);
}
//create vertical 1-9
for (int i = 1; i <= 9; i++)
{
sheet1.CreateRow(i).CreateCell(0).SetCellValue(i);
}
//create the cell formula
for (int iRow = 1; iRow <= 9; iRow++)
{
Row row = sheet1.GetRow(iRow);
for (int iCol = 1; iCol <= 9; iCol++)
{
//the first cell of each row * the first cell of each column
string formula = GetCellPosition(iRow, 0) + "*" + GetCellPosition(0, iCol);
row.CreateCell(iCol).CellFormula = formula;
}
} WriteToFile();
} static string GetCellPosition(int row, int col)
{
col = Convert.ToInt32('A') + col;
row = row+1;
return ((char)col) + row.ToString();
} static void WriteToFile()
{
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(@"test.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
} static void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook(); ////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi; ////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = row.GetCell(j).ToString();
}
table.Rows.Add(dataRow);
}