DataTable---->Excel，填充数据
private IWorkbook workbook = null;
private ISheet sheet = null;
private string fileName = "";//文档路径
private FileStream fs = null; public ExcelHelper()
{
}
//构造函数
public ExcelHelper(string file)
{
this.fileName = file;
}
/// <summary>
/// DataTable到Excel,向Excel模板填充数据
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath">导出EXcel存储地方</param>
/// <param name="templeteFile">Excel模板路径</param>
/// <param name="titleName">sheet标题</param>
public bool ExportExcel(DataTable dt, string filePath, string templeteFile, string titleName)
{
bool flag = false;
try
{
using (MemoryStream ms = ExportExcelForDt(dt, filePath, templeteFile, titleName))
{
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
byte[] data = ms.ToArray();
fs.Write(data, , data.Length);
fs.Flush(); data = null;
}
}
flag = true;
}
catch
{
} return flag;
} private MemoryStream ExportExcelForDt(DataTable dt, string filePath, string templeteFile, string titleName)
{
int totalIndex = ; //每个类别的总行数
int rowIndex = ;//起始行
int dtRowIndex = dt.Rows.Count;
//工作薄
IWorkbook workbook = null;
using (FileStream fs = new FileStream(templeteFile, FileMode.Open, FileAccess.Read))
{
//判断Excel版本
if (System.IO.Path.GetExtension(templeteFile) == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else if (System.IO.Path.GetExtension(templeteFile) == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else
{
throw new Exception("文件类型错误");
} //获取sheet[0]
ISheet sheet = workbook.GetSheetAt(); //表头处理
IRow headerRow = sheet.GetRow();
ICell headerCell = headerRow.GetCell();
headerCell.SetCellValue(titleName); //隐藏多余行，在数据填充区，对空白部分作处理
for (int i = rowIndex + dtRowIndex; i < rowIndex + totalIndex; i++)
{
IRow dataRowH = sheet.GetRow(i);
dataRowH.Height = ;
dataRowH.ZeroHeight = true;
} //填充数据
foreach (DataRow row in dt.Rows)
{
IRow dataRow = sheet.GetRow(rowIndex); //开始列(0为标题列，从1开始)
int columnIndex = ;
#region 2
foreach (DataColumn column in dt.Columns)
{
#region 1
//列序号赋值 2018-02-17 不能要等号，否则最后一项没有
if (columnIndex > dt.Columns.Count)
{
break;
} ICell newCell = dataRow.GetCell(columnIndex);
if (newCell == null)
{
newCell = dataRow.CreateCell(columnIndex);
} string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String":
newCell.SetCellValue(drValue);
break;
case "System.DateTime":
DateTime datev;
DateTime.TryParse(drValue, out datev);
newCell.SetCellValue(datev);
break;
case "System.Boolean":
bool boolV = false;
bool.TryParse(drValue, out boolV);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "Systmen.Byte":
int intV = ;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal":
case "System.Double":
double doubV = ;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull":
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
columnIndex++;
#endregion }
#endregion rowIndex++;
} //格式化当前sheet,用于数据total计算
sheet.ForceFormulaRecalculation = true; using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = ;
sheet = null;
workbook = null; return ms;
}
}
}