NPOI开源地址:http://npoi.codeplex.com/
NPOI教程: http://tonyqus.sinaapp.com/
具体的不在这里写了,感兴趣的可以去官网。
先来说导出的例子
private void ExportExcel()
{
DataTable table_data = MAYIXUE.BLL.NPOIUtility.GetValue(); HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("mayixue的NPOI测试1"); ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
ICellStyle stringStyle = hssfworkbook.CreateCellStyle();
stringStyle.VerticalAlignment = VerticalAlignment.CENTER; //取得列宽
int columnCount = table_data.Columns.Count;
int[] arrColWidth = new int[columnCount];
int width = ;
foreach (DataColumn column in table_data.Columns)
{
arrColWidth[column.Ordinal] = width;
} int rowIndex = ;
string temp_col1 = "";
int col1_s = ; foreach (DataRow row in table_type.Rows)
{
#region 新建表,填充列头,样式
if (rowIndex == || rowIndex == )
{
if (rowIndex != )
{
sheet1 = hssfworkbook.CreateSheet();
} #region 列头及样式 IRow headerRow = sheet1.CreateRow(); ICellStyle headStyle = hssfworkbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
headStyle.VerticalAlignment = VerticalAlignment.CENTER; IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = ;
headStyle.SetFont(font); foreach (DataColumn column in table_type.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToInt32(column.ColumnName)); headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
sheet1.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + ) * );//设置列宽 } #endregion rowIndex = ;
}
#endregion #region 填充内容 int j = ;
IRow dataRow = sheet1.CreateRow(rowIndex);
foreach (DataColumn column in table_data.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
newCell.CellStyle = stringStyle;
break;
case "System.Double":
if (drValue != "")
{
double doubV = ;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
}
else
{
newCell.SetCellValue("");
}
newCell.CellStyle = cellStyle;
break;
} #region 单元格合并(这里只合并第一列) if (j == && temp_col1 != drValue)
{
if (temp_col1 != "")
{
sheet1.AddMergedRegion(new CellRangeAddress(col1_s, rowIndex - , , ));
}
temp_c1 = drValue;
col1_s = rowIndex;
} #endregion j++;
}
#endregion rowIndex++;
} //冻结窗口 锁定表头和第一列
sheet1.CreateFreezePane(, , , ); //输出
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "mayixue的NPOI测试.xls"));
context.Response.Clear(); MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
file.WriteTo(context.Response.OutputStream);
context.Response.End();
}
再来看下导入Excel到DataTable
/// <summary>
/// 读取Excel文件到DataSet中
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
private DataTable ExcelToDataTable(string filePath)
{
DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
ISheet sheet = hssfworkbook.GetSheetAt();
System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow();
int cellCount = headerRow.LastCellNum; for (int j = ; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
} for (int i = (sheet.FirstRowNum + ); 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);
}
return dt;
}
NPOI升级后语法上有些变动(这个例子中红色标注地方),(我是从1.2.2直接更新到2.0的,期间发生过什么这里不做描述)
这个例子的实现,离不开优秀文章的帮助,在这里向作者表示感谢。
参考文章: