/// <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); } }