Npoi简单说明
1、整个Excel表格叫做工作表:WorkBook(工作薄),包含的叫页(工作表):Sheet;行:Row;单元格Cell。
2、NPOI是POI的C#版本,NPOI的行和列的index都是从0开始
3、POI读取Excel有两种格式一个是HSSF,另一个是XSSF。 HSSF和XSSF的区别如下:
HSSF is the POI Project’s pure Java implementation of the Excel ‘97(-2007) file format.
XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
即:HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。
DataTable导出为Excel
后台代码:
public void DataTableToExcel(DataTable dt, string templatePath,string sheetName, string savePath)
{
try
{
FileStream fileStream = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileStream);
HSSFSheet sheet1 = hssfworkbook.GetSheet(sheetName) as HSSFSheet;
//Excel中有50行数据时sheet.LastRowNum返回49
int count = sheet1.LastRowNum + 1;
if (dt.Rows.Count > count - 1)
{
for (int ii = 0; ii < dt.Rows.Count - count + 1; ii++)
{
sheet1.CopyRow(6, count + ii);
}
}
var colCount = dt.Columns.Count;
int rowCount = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
//表头占n行,rowCount=i+n
//当n=2时,dt中的第2行数据,写到Excel文件中的第4行
rowCount = i + 1;
for (int j = 0; j < colCount; j++)
{
string contnts = (dt.Rows[i][j] == DBNull.Value) ? "" : dt.Rows[i][j].ToString();
//判断Excel单元格是否为null
sheet1.GetRow(rowCount).GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK).SetCellValue(contnts);
}
}
sheet1.ForceFormulaRecalculation = true;
FileStream file = new FileStream(savePath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
catch (Exception ex)
{
string error = ex.Message;
throw;
}
}
前台调用:
protected void btnExport_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name", Type.GetType("System.String"));
dt.Columns.Add("Sex", Type.GetType("System.String"));
dt.Columns.Add("Age", Type.GetType("System.Int32"));
dt.Columns.Add("Address", Type.GetType("System.String"));
dt.Columns.Add("PhoneNumber", Type.GetType("System.String"));
for (int i = 0; i < 5; i++)
{
DataRow row = dt.NewRow();
row[0] = "姓名" + i;
row[1] = i % 2 == 0 ? "女" : "男";
row[2] = "18";
row[3] = "葡萄牙罗卡角";
row[4] = "18233399999";
dt.Rows.Add(row);
}
string savePath = Server.MapPath("SavePath/用户信息") + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string templatePath=Server.MapPath("Temp/用户信息.xls");
new NpoiHelper().DataTableToExcel(dt, templatePath, "Sheet1", savePath);
}
Excel导入数据转换为DataTable
后台代码:
public DataTable ExcelToDataTable(DataTable dt, string filePath, string sheetName, int titleCount)
{
try
{
ISheet sheet = null;
IWorkbook workbook = null;
DataTable dtResult = dt;
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (filePath.IndexOf(".xlsx") > 0)
workbook = new XSSFWorkbook(fs);
else if (filePath.IndexOf(".xls") > 0)
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow row = sheet.GetRow(0);
int colCount = row.LastCellNum;
//Excel中有50行数据时sheet.LastRowNum返回49
int rowCount = sheet.LastRowNum + 1;
//Excel中有50行数据时sheet.LastRowNum返回50
int physicalRowCount = sheet.PhysicalNumberOfRows;
int startRow = sheet.FirstRowNum + titleCount;
for (int i = startRow; i < rowCount; i++)
{
row = sheet.GetRow(i);
if (!RowIsValid(row)) continue;
DataRow dataRow = dt.NewRow();
for (int j = 0; j < colCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dtResult.Rows.Add(dataRow);
}
}
return dtResult;
}
catch (Exception ex)
{
string error = ex.Message;
throw;
}
}
/// <summary>
/// 判断Excel中的行是否为空行
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
public bool RowIsValid(IRow row)
{
bool flag = false;
for (int i = 0; i < row.LastCellNum; i++)
{
if (row.GetCell(i) != null)
{
if (!string.IsNullOrEmpty(row.GetCell(i).ToString()))
{
flag = true;
break;
}
}
}
return flag ? true : false;
}
前台调用:
protected void btnImport_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name", Type.GetType("System.String"));
dt.Columns.Add("Sex", Type.GetType("System.String"));
dt.Columns.Add("Age", Type.GetType("System.String"));
dt.Columns.Add("Address", Type.GetType("System.String"));
dt.Columns.Add("PhoneNumber", Type.GetType("System.String"));
/*先把用户选择的文件保存在服务器端*/
string path = Path.Combine(Server.MapPath("UploadPath"), FileUpload1.FileName);
FileUpload1.PostedFile.SaveAs(path);
/*再从服务器端读取Excel文件,转换为DataTable*/
DataTable result = new NpoiHelper().ExcelToDataTable(dt, path, "Sheet1", 1);
int rowCount = result.Rows.Count;
}