npoi excel导入html数据,.NET之NPOI Excel数据导出和批量导入功能

usingNPOI.HSSF.UserModel;usingNPOI.SS.UserModel;usingNPOI.SS.Util;usingNPOI.XSSF.UserModel;usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.IO;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;namespaceUtility

{///

///NPOI操做帮助类///

public classNpoiHelper

{///

///DataTable导出到Excel文件///

/// 源DataTable

/// 表头文本

/// 保存位置

public static void Export(DataTable dtSource, string strHeaderText, stringstrFileName)

{using (MemoryStream ms =Export(dtSource, strHeaderText))

{using (FileStream fs = newFileStream(strFileName, FileMode.Create, FileAccess.Write))

{//数据填写

byte[] data =ms.ToArray();

fs.Write(data,0, data.Length);

fs.Flush();

}

}

}///

///DataTable导出到Excel的MemoryStream///

/// 源DataTable

/// 表头文本

public static MemoryStream Export(DataTable dtSource, stringstrHeaderText)

{

HSSFWorkbook workbook= newHSSFWorkbook();

ISheet sheet=workbook.CreateSheet();

ICellStyle dateStyle=workbook.CreateCellStyle();

IDataFormat format=workbook.CreateDataFormat();

dateStyle.DataFormat= format.GetFormat("yyyy-MM-dd");#region 取得每列的列宽(最大宽度)

int[] arrColWidth = new int[dtSource.Columns.Count];foreach (DataColumn item indtSource.Columns)

{//GBK对应的code page是CP936

arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;

}for (int i = 0; i < dtSource.Rows.Count; i++)

{for (int j = 0; j < dtSource.Columns.Count; j++)

{int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;if (intTemp >arrColWidth[j])

{

arrColWidth[j]=intTemp;

}

}

}#endregion

int rowIndex = 0;foreach (DataRow row indtSource.Rows)

{#region 新建表，填充表头，填充列头，样式

if (rowIndex == 65535 || rowIndex == 0)

{if (rowIndex != 0)

{

sheet=workbook.CreateSheet();

}#region 表头及样式{

IRow headerRow= sheet.CreateRow(0);

headerRow.HeightInPoints= 25;

headerRow.CreateCell(0).SetCellValue(strHeaderText);//第一列表头名称

ICellStyle headStyle=workbook.CreateCellStyle();

headStyle.Alignment=HorizontalAlignment.Center;

IFont font=workbook.CreateFont();

font.FontHeightInPoints= 20;

font.Boldweight= 700;

headStyle.SetFont(font);

headerRow.GetCell(0).CellStyle =headStyle;

sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));

}#endregion

#region 列头及样式{//也可自定义标题名称，填写到 headerRow.CreateCell(1).SetCellValue();需使用directory先填写标题，而后遍历操做中便可

IRow headerRow = sheet.CreateRow(1);

ICellStyle headStyle=workbook.CreateCellStyle();

headStyle.Alignment=HorizontalAlignment.Center;

IFont font=workbook.CreateFont();

font.FontHeightInPoints= 10;

font.Boldweight= 700;

headStyle.SetFont(font);foreach (DataColumn column indtSource.Columns)

{

headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

headerRow.GetCell(column.Ordinal).CellStyle=headStyle;//设置列宽

sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

}

}#endregionrowIndex= 2;

}#endregion

#region 填充内容ICellStyle contentStyle=workbook.CreateCellStyle();

contentStyle.Alignment=HorizontalAlignment.Left;

IRow dataRow=sheet.CreateRow(rowIndex);foreach (DataColumn column indtSource.Columns)

{

ICell newCell=dataRow.CreateCell(column.Ordinal);

newCell.CellStyle=contentStyle;string drValue =row[column].ToString();switch(column.DataType.ToString())

{case "System.String"://字符串类型

newCell.SetCellValue(drValue);break;case "System.DateTime"://日期类型

DateTime dateV;

DateTime.TryParse(drValue,outdateV);

newCell.SetCellValue(dateV);

newCell.CellStyle= dateStyle;//格式化显示

break;case "System.Boolean"://布尔型

bool boolV = false;bool.TryParse(drValue, outboolV);

newCell.SetCellValue(boolV);break;case "System.Int16"://整型

case "System.Int32":case "System.Int64":case "System.Byte":int intV = 0;int.TryParse(drValue, outintV);

newCell.SetCellValue(intV);break;case "System.Decimal"://浮点型

case "System.Double":double doubV = 0;double.TryParse(drValue, outdoubV);

newCell.SetCellValue(doubV);break;case "System.DBNull"://空值处理

newCell.SetCellValue("");break;default:

newCell.SetCellValue("");break;

}

}#endregionrowIndex++;

}using (MemoryStream ms = newMemoryStream())

{

workbook.Write(ms);

ms.Flush();

ms.Position= 0;//sheet.Dispose();//workbook.Dispose();//通常只用写这一个就OK了，他会遍历并释放全部资源，但当前版本有问题因此只释放sheet

returnms;

}

}///

///读取excel,将数据Excel数据源转化为datatable类型///默认第一行为标头///

/// excel文档路径

///

public static DataTable Import(string strFileName,stringFileType)

{

IWorkbook hssfworkbook;

DataTable dt= newDataTable();//HSSFWorkbook hssfworkbook;

using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))//数据读取

{XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式

//不一样格式excle判断

if (FileType == "xls")

{

hssfworkbook= newHSSFWorkbook(file);

}else{

hssfworkbook= newXSSFWorkbook(file);

}

}

ISheet sheet= hssfworkbook.GetSheetAt(0);

System.Collections.IEnumerator rows=sheet.GetRowEnumerator();

IRow headerRow= sheet.GetRow(0);int cellCount =headerRow.LastCellNum;for (int j = 0; j < cellCount; j++)

{

ICell cell=headerRow.GetCell(j);

dt.Columns.Add(cell.ToString());

}for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)

{

IRow row=sheet.GetRow(i);

DataRow dataRow=dt.NewRow();for (int j = row.FirstCellNum; j < cellCount; j++)

{if (row.GetCell(j) != null)

dataRow[j]=row.GetCell(j).ToString();

}

dt.Rows.Add(dataRow);

}returndt;

}

}

}