1.导出.xls格式
if (dispatches != null)
{
#region
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "明细列表.xls(" + DateTime.Now.ToString("yyyy-MM-dd") + ")";
Cells cell = sheet.Cells;
cell.SetColumnWidth(0, 15);
cell.SetColumnWidth(1, 50);
cell.SetColumnWidth(2, 50);
cell.SetColumnWidth(3, 30);
cell.SetColumnWidth(4, 50);
cell.SetColumnWidth(5, 15);
cell.SetColumnWidth(6, 15);
cell.SetColumnWidth(7, 15);
cell.SetColumnWidth(8, 20);
cell.SetColumnWidth(9, 20);
cell[0, 0].PutValue("派工类型");
cell[0, 1].PutValue("项目名称");
cell[0, 2].PutValue("任务名称");
cell[0, 3].PutValue("派工单号");
cell[0, 4].PutValue("合作单位");
cell[0, 5].PutValue("合作单位负责人");
cell[0, 6].PutValue("合作单位实施人员");
cell[0, 7].PutValue("提交人");
cell[0, 8].PutValue("提交时间");
cell[0, 9].PutValue("流程状态");
Aspose.Cells.Style styles = workbook.Styles[workbook.Styles.Add()];
styles.HorizontalAlignment = TextAlignmentType.Center;
styles.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
styles.Pattern = BackgroundType.Solid;
styles.Font.IsBold = true;
cell.SetRowHeight(0, 30);
styles.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
styles.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
styles.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
styles.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
#endregion
for (int i = 0; i < 10; i++)
{
cell[0, i].SetStyle(styles);
}
for (int i = 0; i < dispatches.Count; i++)
{
cell.SetRowHeight(i + 1, 30);
cell[i + 1, 0].PutValue(dispatches[i].DispatchingType.GetText());
ProjectHisDoc d = doc.Where(s => s.ProjectNumber == dispatches[i].ProjectNumber).FirstOrDefault();
cell[i + 1, 1].PutValue(string.Format("[{0}]{1}", dispatches[i].ProjectNumber, d == null ? "" : d.ProjectName));
string taskname = string.Empty;
if (task.Count > 0)
{
foreach (var t in task)
{
taskname += "[" + t.Number + "]" + t.Name + ";\n";
}
}
cell[i + 1, 2].PutValue(taskname);
cell[i + 1, 3].PutValue(dispatches[i].DispatchNumber);
cell[i + 1, 4].PutValue(supplier.Name);
cell[i + 1, 5].PutValue(se.Name);
cell[i + 1, 6].PutValue(sue.Name);
cell[i + 1, 7].PutValue(user.ChineseName);
cell[i + 1, 8].PutValue(dispatches[i].DispatchDateTime.ToString("yyyy-MM-dd"));
cell[i + 1, 9].PutValue(statusDescription);
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()];
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.IsTextWrapped = true;
cell[i + 1, 0].SetStyle(style);
cell[i + 1, 1].SetStyle(style);
cell[i + 1, 2].SetStyle(style);
cell[i + 1, 3].SetStyle(style);
cell[i + 1, 4].SetStyle(style);
cell[i + 1, 5].SetStyle(style);
cell[i + 1, 6].SetStyle(style);
cell[i + 1, 7].SetStyle(style);
cell[i + 1, 8].SetStyle(style);
cell[i + 1, 9].SetStyle(style);
}
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlDecode("明细列表.xls"));
workbook.Save(Response.OutputStream, SaveFormat.Excel97To2003);
Response.End();
}
excel常用方法：https://docs.microsoft.com/zh-cn/office/vba/api/word.cell
2.导出csv格式
protected void btExport_OnClick(object sender, EventArgs e)
{
string region = ddlRegion.SelectedValue;
List<AppraisalsData> appraisalsDatas = GetAppraisalsData(region);
StringBuilder sb = new StringBuilder();
if (appraisalsDatas.Count > 0)
{
sb.AppendLine(
string.Format("\"建设单位\",\"计划删除或撤项\",\"实际删除或撤项\",\"要求归档\",\"实际归档\",\"要求报审\",\"实际报审\",\"要求完工\",\"实际完工\",\"项目整改完成率\",\"要求完备信息\",\"实际填报信息\",\"项目数据完整率\",\"项目整改及时率\",\"未完整项目\""));
foreach (AppraisalsData item in appraisalsDatas)
{
sb.AppendLine(
string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\",\"{11}\",\"{12}\",\"{13}\",\"{14}\"",
item.Region,
item.ReDelCount,
item.ActDelCount,
item.ReFinishCount,
item.ActFinishCount,
item.ReAuditCount,
item.ActAuditCount,
item.ReWorkCount,
item.ActWorkCount,
item.IntegrityRate,
item.ReIntegrityCount,
item.ActIntegrityCount,
item.ReFinishRate,
item.RegularlyRate,
item.ProblematicCount
));
}
HttpContext.Current.Response.SaveAsUTF8CSVFile(sb.ToString(), "项目整改及时率.csv");
}
}
3.创建导出excel
//创建Excel工作簿
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet("机房统计(建设批次)");
//创建行
HSSFRow newHeaderRow = (HSSFRow)sheet.CreateRow(0);
//创捷单元格
for (int i = 0; i < listHead_2.Count; i++)
{
if (i == 0)
newHeaderRow.CreateCell(i).SetCellValue("地市");
else
{
if (i % 2 != 0)
{
newHeaderRow.CreateCell(i).SetCellValue(listHead_1[(i - 1) / 2] + "");
}
else
{
newHeaderRow.CreateCell(i);
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, i - 1, i));
}
}
}
//创建行
HSSFRow newHeaderRow_2 = (HSSFRow)sheet.CreateRow(1);
for (int i = 0; i < listHead_2.Count; i++)
{
if (i != 0)
newHeaderRow_2.CreateCell(i).SetCellValue((listHead_2[i] + "").Split('_')[1]);
else
{
newHeaderRow_2.CreateCell(i);
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 1, i, i));
}
}
//写入数据
for (int i = 0; i < listBody.Count; i++)
{
//创建行
HSSFRow newRow = (HSSFRow)sheet.CreateRow(i + 2);
for (int j = 0; j < listBody[i].Count; j++)
{
newRow.CreateCell(j).SetCellValue(listBody[i][j]);
}
}
//创建流
MemoryStream ms = new MemoryStream();
//工作簿写入流
hssfworkbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
Context.Response.AddHeader("Content-Disposition", "attachment;fileName=机房统计-建设批次("+DateTime.Now.ToString("yyyy-MM-dd")+").xls");
Context.Response.BinaryWrite(ms.ToArray());
导出合并单元格
具体合并描述：
Cells.merge(a,b,c,d) 单元格合并函数
a 单元格的列号
b 单元格的行号
c 从单元格[a,b]起，向下合并到c列
d 从单元格[a,b]起，向下合并到d行
注：单元格的列号和行号都是从0开始计
