/// <summary>
/// 将数据生成为excel文件
/// <param name="info">配置信息</param>
/// <param name="fileName">文件名</param>
/// </summary>
public void WriteWorkbook(SheetInfo info, string fileName)
{
ISheet sheet = null;
XSSFWorkbook workbook1 = null;
HSSFWorkbook workbook2 = null;
try
{
//创建工作簿
if (string.IsNullOrEmpty(info.TemplateFile) == true)
{
//不使用模板
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1 = new XSSFWorkbook();
sheet = workbook1.CreateSheet(info.SheetName);
}
else
{
workbook2 = new HSSFWorkbook();
sheet = workbook2.CreateSheet(info.SheetName);
}
}
else
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
//使用模板
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1 = new XSSFWorkbook(fs);
sheet = workbook1.GetSheet(info.SheetName);
}
else
{
workbook2 = new HSSFWorkbook(fs);
sheet = workbook2.GetSheet(info.SheetName);
}
}
File.Delete(fileName);
}
//写入文件
if (string.IsNullOrEmpty(info.TemplateFile) == true)
{
FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
try
{
#region 无模板
//写入列标题
IRow headRow = sheet.CreateRow(0);
for (int i = 0; i < info.ColInfos.Count; i++)
{
headRow.CreateCell(i).SetCellValue(info.ColInfos[i].ColumnTitle);
headRow.Cells[i].CellStyle.Alignment = HorizontalAlignment.Center;
if (info.ColInfos[i].ColumnWidth != 0)
{
sheet.SetColumnWidth(i, Convert.ToInt32((info.ColInfos[i].ColumnWidth / 3) * 0.4374 * 256));
}
}
//写入行数据
int rowsNum = 1;
IRow dataRow = null;
foreach (List <CellInfo> items in info.DataArray)
{
dataRow = sheet.CreateRow(rowsNum);
for (int i = 0; i < items.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(items[i].Value);
}
rowsNum++;
}
#endregion
info.DataArray.Clear();
info.Dispose();
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1.Write(fs);
}
else
{
workbook2.Write(fs);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
fs.Close();
fs.Dispose();
GC.Collect();
}
}
else
{
FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
try
{
#region 模板
int rowIndex = 0;
List <ICell> firstCells = new List <ICell>();
int count = info.DataArray.Count;
for (int k = 0; k < count; k++)
{
List <CellInfo> items = info.DataArray[0] as List <CellInfo>;
rowIndex++;
for (int i = 0; i < items.Count; i++)
{
IRow row = sheet.GetRow(int.Parse(items[i].YPosition));
if (row == null)
{
row = sheet.CreateRow(int.Parse(items[i].YPosition));
}
ICell cell = row.GetCell(int.Parse(items[i].XPosition));
if (rowIndex == 1)
{
firstCells.Add(cell);
}
if (cell == null)
{
cell = row.CreateCell(int.Parse(items[i].XPosition));
}
if (firstCells[i] != null)
{
cell.CellStyle = firstCells[i].CellStyle;
}
if (items[i].Value != "")
{
switch (items[i].DataType.ToLower())
{
case "datetime":
cell.SetCellValue(DateTime.Parse(items[i].Value));
break;
case "number":
cell.SetCellValue(double.Parse(items[i].Value));
break;
default:
cell.SetCellValue(items[i].Value);
break;
}
}
}
items.Clear();
items = null;
info.DataArray.RemoveAt(0);
#region 分段保存
if ((rowIndex % 20000) == 0)
{
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1.Write(fs);
}
else
{
workbook2.Write(fs);
}
fs.Close();
fs.Dispose();
GC.Collect();
if (k <= count - 1)
{
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
}
}
#endregion
}
#endregion
info.DataArray.Clear();
info.Dispose();
if (fileName.IndexOf(".xlsx") > 0)
{
workbook1.Write(fs);
}
else
{
workbook2.Write(fs);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (fs != null)
{
fs.Close();
fs.Dispose();
GC.Collect();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
