导出Excel表格
导出Excel表格可以使用Office的插件,但我们现场用的电脑不一定是装过Office的,所以也就不能用Office的插件。一开始,我采用了csv格式的文件,直接把后缀改成了xls。这样Excel当然是会打开的。但csv格式太过简单,打开的时候往往不怎么好看。后来终于找到了NPOI这个第三方库,用起来还不错。
部分代码如下:
public static MemoryStream DataTableToExcel(DataTable OriginData, DataTable StatisticData, HeaderClass header)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
sheet = (HSSFSheet)workbook.CreateSheet();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
HSSFFont fontt = (HSSFFont)workbook.CreateFont();
fontt.FontHeightInPoints = 12;
fontt.Boldweight = 700;
style = GetCellStyle(workbook, null, null, FillPatternType.NO_FILL, null, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, false, false);
titleStyle = GetCellStyle(workbook, fontt, null, FillPatternType.NO_FILL, null, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, false, true);
headStyle = GetCellStyle(workbook, font, null, FillPatternType.NO_FILL, null, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, false, false);
dateStyle = GetCellStyle(workbook, null, null, FillPatternType.NO_FILL, null, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, true, false);
int i = 0;
int j = 0;
#region 新建表,填充表头,填充列头,样式
InitHeader(header, OriginData);
#endregion
#region 填充内容
for (i = 0; i < OriginData.Rows.Count; i++)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(total_row + i);
for (j = 0; j < OriginData.Columns.Count; j++)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(j);
string drValue = OriginData.Rows[i][j].ToString();
switch (j)
{
case 0:
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;
break;
default:
newCell.SetCellValue(drValue);
newCell.CellStyle = style;
break;
}
Global.mWaitWnd.ShowProgress(OriginData.Rows.Count, i);
}
}
#endregion
#region 填充统计值
if (StatisticData != null)
{
for (i = 0; i < 4; i++)
{
HSSFRow dataRowS = (HSSFRow)sheet.CreateRow(total_row + OriginData.Rows.Count + i);
for (j = 0; j < StatisticData.Columns.Count; j++)
{
HSSFCell newCell = (HSSFCell)dataRowS.CreateCell(j);
string drValue = StatisticData.Rows[i][j].ToString();
newCell.SetCellValue(drValue);
newCell.CellStyle = style;
}
}
}
Global.mWaitWnd.ShowProgress(100, 100);
#endregion
#region 写入文件
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
return ms;
}
#endregion
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.ToString());
return null;
}
}