private static void InitHeader(ISheet sheet, int lastCol, ExcelData data)
{
var r = sheet.CreateRow(0);
ICell c;
for (int i = 0; i <= lastCol; i++)
{
c = r.CreateCell(i);
if (i == 0)
{
c.SetCellValue("线路：");
c.CellStyle = CellStyle;
}
else if (i == 2)
{
c.SetCellValue("数据日期：");
c.CellStyle = LeftAlignStyle;
}
else if (i == 8)
{
c.SetCellValue(string.Format("数据量：{0} 百分比：{1:P}", data.Total, data.Ratio));
c.CellStyle = LeftAlignStyle;
}
else
{
c.CellStyle = CellStyle;
}
}
r = sheet.CreateRow(1);
for (int i = 0; i <= lastCol; i++)
{
c = r.CreateCell(i);
if (i == 0)
{
c.SetCellValue("次数");
c.CellStyle = BoldStyle;
}
else if (i == 2)
{
c.SetCellValue("实际行程时间（分钟）");
c.CellStyle = BoldStyle;
}
else
{
c.CellStyle = CellStyle;
}
}
}
public static void Export(DataTable dt, ExcelData data)
{
// create a new sheet
var sheet = Wb.CreateSheet();
// declare a row object reference
// declare a cell object reference
ICell c;
int lastCol = dt.Columns.Count;
InitHeader(sheet, lastCol, data);
// Titles
var r = sheet.CreateRow(2);
for (int i = 0; i < lastCol; i++)
{
c = r.CreateCell(i + 1);
if (i > 0)
{
c.SetCellValue(dt.Columns[i].ToString().Replace(" ", ""));
}
c.CellStyle = BoldStyle;
}
for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
{
r = sheet.CreateRow(rowNum + 3);
var row = dt.Rows[rowNum];
c = r.CreateCell(0);
if (rowNum == 0)
{
c.SetCellValue("预计行程时间-实际行程时间（分钟）");
}
c.CellStyle = CellStyle;
for (int cellNum = 0; cellNum < lastCol; cellNum++)
{
c = r.CreateCell(cellNum + 1);
var val = row[cellNum];
int intVal;
if (int.TryParse(val.ToString(), out intVal))
{
c.SetCellValue(intVal);
c.CellStyle = intVal > 0 ? FilledStyle : CellStyle;
}
else
{
c.SetCellValue(val.ToString());
c.CellStyle = BoldStyle;
}
}
}
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 1));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 2, 7));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 8, lastCol));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 1));
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 2, lastCol));
sheet.AddMergedRegion(new CellRangeAddress(3, dt.Rows.Count + 2, 0, 0));
for (int i = 0; i < lastCol; i++)
{
//设置自适应宽度
sheet.AutoSizeColumn(i);
}
// Save
var fs = new FileStream(data.FileName.Replace(".txt", "") + ".xls", FileMode.OpenOrCreate);
Wb.Write(fs);
fs.Close();
}
