?using System;
using System.Collections.Generic;
using System.Web;
using NPOI;
using NPOI.HSSF;
using NPOI.HSSF.Util;
using https://www.doczj.com/doc/bc7489045.html,erModel;
using System.IO;
using System.Data;
using System.Text;
///
///OperatorExcel 的摘要说明
///
///
/// 简单的导出，导入excel
///
public class OperatorExcel
{
public OperatorExcel()
{ }
///
/// 读取excel到datatable
///
/// excel路径
/// excel表头行号
///
public static DataTable InputExcel(string excelPath,string sheetName, int theaderIdx)
{
using (Stream s = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
try
{
https://www.doczj.com/doc/bc7489045.html,erModel.HSSFWorkbook book = new HSSFWorkbook(s);
return InputExcel(book, sheetName,theaderIdx);
}
catch
{
return null;
}
}
}
///
/// 导入excel到datatable
///
/// excel路径
/// excel表头行号
///
public static DataTable InputExcel(Stream s,string sheetName, int theaderIdx)
{
https://www.doczj.com/doc/bc7489045.html,erModel.HSSFWorkbook book = new HSSFWorkbook(s);
return InputExcel(book, sheetName,theaderIdx);
}
///
/// 读取excel到datatable
///
/// excel工作薄
/// sheet名称
/// excel中对应的表头行号（从0开始,要求都是字符串格式）
///
private static DataTable InputExcel(HSSFWorkbook book,string sheetName, int theaderIdx)
{
System.Text.RegularExpressions.Regex re = new System.Text.RegularExpressions.Regex(@"\n");
https://www.doczj.com/doc/bc7489045.html,erModel.Sheet sheet = book.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
///创建datatable
DataTable dt = new DataTable();
https://www.doczj.com/doc/bc7489045.html,erModel.Row rowHeader = sheet.GetRow(theaderIdx);
for (int i = 0; i < https://www.doczj.com/doc/bc7489045.html,stCellNum; i++)
{
string title = rowHeader.GetCell(i).StringCellValue;
DataColumn col = new DataColumn(title.Trim());
dt.Columns.Add(col);
}
while (rows.MoveNext())
{
https://www.doczj.com/doc/bc7489045.html,erModel.Row row = (https://www.doczj.com/doc/bc7489045.html,erModel.Row)rows.Current;
if (row.RowNum <= theaderIdx)
continue;
DataRow dr = dt.NewRow();
dr.BeginEdit();
for (int i = 0; i < https://www.doczj.com/doc/bc7489045.html,stCellNum; i++)
{
https://www.doczj.com/doc/bc7489045.html,erModel.Cell cell = row.GetCell(i)
;
if (cell == null)
continue;
switch (cell.CellType)
{
case https://www.doczj.com/doc/bc7489045.html,erModel.CellType.BLANK:
dr[i] = "";
break;
case https://www.doczj.com/doc/bc7489045.html,erModel.CellType.BOOLEAN:
dr[i] = cell.BooleanCellValue;
break;
case https://www.doczj.com/doc/bc7489045.html,erModel.CellType.NUMERIC:
dr[i] = re.Replace(cell.ToString(), "").Trim();
break;
case https://www.doczj.com/doc/bc7489045.html,erModel.CellType.STRING:
dr[i] = (re.Replace(cell.StringCellValue, "").Replace("．", ".")).Trim();
break;
case https://www.doczj.com/doc/bc7489045.html,erModel.CellType.ERROR:
dr[i] = cell.ErrorCellValue;
break;
case https://www.doczj.com/doc/bc7489045.html,erModel.CellType.FORMULA:
dr[i] = cell.NumericCellValue;
break;
default:
dr[i] = cell.CellFormula;
break;
}
}
dr.EndEdit();
dt.Rows.Add(dr);
}
return dt;
}
///
/// 创建excelbook
///
/// datatable
/// sheet名称
/// sheet标题
///
public static HSSFWorkbook CreateBook(DataTable dt, string sheetName, string firstTitle)
{
https://www.doczj.com/doc/bc7489045.html,erModel.HSSFWorkbook book = new HSSFWorkbook();
https://www.doczj.com/doc/bc7489045.html,erModel.Sheet sheet = book.CreateSheet(sheetName);
////样式
https://www.doczj.com/doc/bc7489045.html,erModel.CellStyle cellStyle = book.CreateCellStyle();
cellStyle.Alignment = https://www.doczj.com/doc/bc7489045.html,erModel.HorizontalAlignment.CENTER;
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;
cellStyle.VerticalAlignment = https://www.doczj.com/doc/bc7489045.html,erModel.VerticalAlignment.CENTER;
https://www.doczj.com/doc/bc7489045.html,erModel.Font font = book.CreateFont();
font.FontHeightInPoints = 12;
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index;
cellStyle.SetFont(font);
cellStyle.BorderBottom = https://www.doczj.com/doc/bc7489045.html,erModel.CellBorderType.THIN;
cellStyle.BorderLeft = https://www.doczj.com/doc/bc7489045.html,erModel.CellBorderType.THIN;
cellStyle.BorderRight = https://www.doczj.com/doc/bc7489045.html,erModel.CellBorderType.THIN;
cellStyle.BorderTop = https://www.doczj.com/doc/bc7489045.html,erModel.CellBorderType.THIN;
cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
cellStyle.LeftBorderColor = NPOI.HSSF.Uti
l.HSSFColor.BLACK.index;
///创建一级表头
https://www.doczj.com/doc/bc7489045.html,erModel.Row firstRow = sheet.CreateRow(0);
firstRow.HeightInPoints = 16;
https://www.doczj.com/doc/bc7489045.html,erModel.Cell cell = firstRow.CreateCell(0);
cell.SetCellValue(firstTitle);
cell.CellStyle = cellStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
///创建二级表头
https://www.doczj.com/doc/bc7489045.html,erModel.Row secondRow = sheet.CreateRow(1);
secondRow.HeightInPoints = 16;
https://www.doczj.com/doc/bc7489045.html,erModel.Cell tmpCell;
for (int i = 0; i < dt.Columns.Count; i++)
{
tmpCell = secondRow.CreateCell(i);
string colName = dt.Columns[i].ColumnName;
tmpCell.CellStyle = cellStyle;
tmpCell.SetCellValue(colName);
//设置列宽度
sheet.SetColumnWidth(i, (colName.Length + 4) * 600);
}
///填充数据
int j = 0;
https://www.doczj.com/doc/bc7489045.html,erModel.Font font1 = book.CreateFont();
font1.FontHeightInPoints = 10;
https://www.doczj.com/doc/bc7489045.html,erModel.CellStyle cellStyle1 = book.CreateCellStyle();
cellStyle1.CloneStyleFrom(cellStyle);
cellStyle1.SetFont(font1);
//cellStyle1.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
//cellStyle1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
foreach (DataRow dr in dt.Rows)
{
https://www.doczj.com/doc/bc7489045.html,erModel.Row tmprow = sheet.CreateRow(2 + j);
j++;
for (int i = 0; i < dt.Columns.Count; i++)
{
string value = dr[i] != null ? dr[i].ToString() : "[null]";
if (value != "[null]")
{
https://www.doczj.com/doc/bc7489045.html,erModel.Cell tmpcell = tmprow.CreateCell(i);
tmpcell.CellStyle = cellStyle1;
tmpcell.SetCellValue(value);
}
}
}
return book;
}
///
/// 根据传入路径创建一个excel
///
/// datatable数据
///路径（含文件名）
public static void OutPutExcel(DataTable dt, string sheetName, string firstTitle, string filePath)
{
https://www.doczj.com/doc/bc7489045.html,erModel.HSSFWorkbook book = CreateBook(dt, sheetName, firstTitle);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
book.Write(fs);
}
}
///
/// 将datatable创建为excel，以内存流的形式返回
///
/// datatable
/// sheet名称
/// sheet标题
///
public static MemoryStream OutPutExcelOnStream(DataTable dt, string sheetName, string firstTitle)
{
MemoryStream mstream
= new MemoryStream();
https://www.doczj.com/doc/bc7489045.html,erModel.HSSFWorkbook book = CreateBook(dt, sheetName, firstTitle);
book.Write(mstream);
return mstream;
}
}