/// <summary>
/// 导出excel文件
/// </summary
private void btnExport_Click(object sender, RoutedEventArgs e)
{
try
{
var list = PackageList.Select(f => f.InvoiceList).ToList();
if (list == null || list.Count == 0)
{
MessageBox.Show("未输入任何内容！");
return;
}
SaveFileDialog fileDialog = new SaveFileDialog();
fileDialog.RestoreDirectory = true;
fileDialog.Filter = "Excel(2007-2013)|*.xlsx|Excel(97-2003)|*.xls";
if (fileDialog.ShowDialog() != true)
{
return;
}
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
IRow rowHead = sheet.CreateRow(0);
int index = 0;
rowHead.CreateCell(index++, CellType.String).SetCellValue("快递单号");
rowHead.CreateCell(index++, CellType.String).SetCellValue("张数");
rowHead.CreateCell(index++, CellType.String).SetCellValue("发票序号");
rowHead.CreateCell(index++, CellType.String).SetCellValue("发票代码");
rowHead.CreateCell(index++, CellType.String).SetCellValue("发票号码");
rowHead.CreateCell(index++, CellType.String).SetCellValue("合计金额");
rowHead.CreateCell(index++, CellType.String).SetCellValue("开票日期");
rowHead.CreateCell(index++, CellType.String).SetCellValue("扫描日期");
rowHead.CreateCell(index++, CellType.String).SetCellValue("扫描时间");
rowHead.CreateCell(index++, CellType.String).SetCellValue("备注");
rowHead.CreateCell(index++, CellType.String).SetCellValue("张数/份");
rowHead.CreateCell(index++, CellType.String).SetCellValue("公司");
rowHead.CreateCell(index++, CellType.String).SetCellValue("Flow");
rowHead.CreateCell(index++, CellType.String).SetCellValue("专票");
rowHead.CreateCell(index++, CellType.String).SetCellValue("备注1");
rowHead.CreateCell(index++, CellType.String).SetCellValue("备注2");
int startRow = 1;
int columnIndex = 0;
//填写内容
for (int i = 0; i < PackageList.Count; i++)
{
if (PackageList[i].InvoiceList.Count == 0)
{
continue;
}
for (int j = 0; j < PackageList[i].InvoiceList.Count; j++)
{
var item = PackageList[i].InvoiceList[j];
IRow row = sheet.CreateRow(startRow + j);
columnIndex = 0;
if (j == 0)
{
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.PkgNumber);
}
else
{
columnIndex++;
}
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.PkgIndex);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.RowNumber);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.Code);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.Number);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.Amount.ToString());
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.MakeDate);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.ScanDate);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.ScanTime);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.Remark);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.PageCount);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.Company);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.Flow);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.SpecialTicket);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.Remark1);
row.CreateCell(columnIndex++, CellType.String).SetCellValue(item.Remark2);
}
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startRow, startRow + PackageList[i].InvoiceList.Count - 1, 0, 0));
startRow += PackageList[i].InvoiceList.Count;
}
for (int i = 0; i < columnIndex; i++)
{
sheet.AutoSizeColumn(i);
}
sheet.DisplayGridlines = true;
using (FileStream stream = File.OpenWrite(fileDialog.FileName))
{
workbook.Write(stream);
stream.Close();
}
MessageBox.Show("导出数据成功!", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show("导出失败，请检查文件是否被占用!", "操作提示", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
