导入Aspose.Cells模块
方式1：
/// <summary> /// 将3个DataGridView导出Excel /// </summary> /// <param name="fileName"></param> /// <param name="dgv1"></param> public static void ExportExcel(string fileName, DataGridViewX dgv1, DataGridViewX dgv2, DataGridViewX dgv3) { int total = dgv1.Rows.Count; Program.MF.Msg(GlobalConst.MSG_COLOR_DEFAULT, "开始导出..."); Workbook wb = new Aspose.Cells.Workbook(); Worksheet ws = wb.Worksheets[0]; Cells cell = ws.Cells; //设置字体样式 //Style style1 = wb.Styles[wb.Styles.Add()]; //style1.HorizontalAlignment = TextAlignmentType.Center;//文字居中 //style1.Font.Name = "宋体"; //style1.Font.IsBold = true;//设置粗体 //style1.Font.Size = 12;//设置字体大小 //Style style2 = wb.Styles[wb.Styles.Add()]; //style2.HorizontalAlignment = TextAlignmentType.Center; //style2.Font.Size = 12; //设置字体样式 Style style_title = wb.Styles[wb.Styles.Add()]; style_title.HorizontalAlignment = TextAlignmentType.Left;//文字居左 style_title.Font.Name = "黑体"; style_title.Font.IsBold = true;//设置粗体 style_title.Font.Size = 11;//设置字体大小 style_title.Font.Color = System.Drawing.Color.Red; Style style_head = wb.Styles[wb.Styles.Add()]; style_head.HorizontalAlignment = TextAlignmentType.Center;//文字居中 style_head.Font.Name = "宋体"; style_head.Font.IsBold = true;//设置粗体 style_head.Font.Size = 11;//设置字体大小 style_head.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style_head.Borders[BorderType.TopBorder].Color = Color.Black; style_head.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; style_head.Borders[BorderType.BottomBorder].Color = Color.Black; style_head.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style_head.Borders[BorderType.LeftBorder].Color = Color.Black; style_head.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style_head.Borders[BorderType.RightBorder].Color = Color.Black; Style style_content = wb.Styles[wb.Styles.Add()]; style_content.HorizontalAlignment = TextAlignmentType.Left; style_content.Font.Name = "宋体"; style_content.Font.IsBold = false;//设置粗体 style_content.Font.Size = 11;//设置字体大小 style_content.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; style_content.Borders[BorderType.TopBorder].Color = Color.Black; style_content.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; style_content.Borders[BorderType.BottomBorder].Color = Color.Black; style_content.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; style_content.Borders[BorderType.LeftBorder].Color = Color.Black; style_content.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; style_content.Borders[BorderType.RightBorder].Color = Color.Black; int pos = 0;//sheet中的pos ExcelFun(dgv1, style_head, style_content, cell, pos); ExcelFun(dgv2, style_head, style_content, cell, dgv1.RowCount + 3); ExcelFun(dgv3, style_head, style_content, cell, dgv1.RowCount + dgv2.RowCount + 6); try { wb.Save(fileName); Program.MF.Msg(GlobalConst.MSG_COLOR_DEFAULT, "导出完成..."); } catch (Exception ex) { // Console.WriteLine(ex.Message); } } private static void ExcelFun(DataGridView dgv, Style style_head, Style style_content, Cells cell, int pos) { int invisibleCount = 0; for (int i = 0; i < dgv.Columns.Count; i++) { if (dgv.Columns[i].Visible)// && (dgv.Columns[i] as DataGridViewButtonXColumn) == null) { cell[pos, i - invisibleCount].PutValue(dgv.Columns[i].HeaderText); cell[pos, i - invisibleCount].SetStyle(style_head); if (i == 0) { cell.SetColumnWidth(i - invisibleCount, 8); } else { cell.SetColumnWidth(i - invisibleCount, 23); } } else { invisibleCount += 1; } } pos++; for (int j = 0; j < dgv.Rows.Count; j++) { invisibleCount = 0; for (int i = 0; i < dgv.Columns.Count; i++) { if (dgv.Columns[i].Visible) { if (dgv.Rows[j].Cells[i].Value != null) { if (dgv.Rows[j].Cells[i].Value.GetType() == typeof(DateTime)) { cell[pos + j, i - invisibleCount].PutValue(((DateTime)dgv.Rows[j].Cells[i].Value).ToString("yyyy-MM-dd HH:mm:ss")); } else { if (!"查看".Equals(dgv.Rows[j].Cells[i].Value)) { cell[pos + j, i - invisibleCount].PutValue(dgv.Rows[j].Cells[i].Value); } else { cell[pos + j, i - invisibleCount].PutValue("-"); } } } else { cell[pos + j, i - invisibleCount].PutValue("-"); } cell[pos + j, i - invisibleCount].SetStyle(style_content); } else { invisibleCount += 1; } } } }
方式2：
private void DownExcelThread(string src) { Program.MF.Msg(GlobalConst.MSG_COLOR_DEFAULT, "开始下载..."); Workbook wb = new Workbook(); Worksheet ws = wb.Worksheets[0]; Cells cells = ws.Cells; if (listOsptu.Count > 0) { cells[0, 0].Value = "序号"; cells[0, 1].Value = "用户ID"; cells[0, 2].Value = "订单号"; cells[0, 3].Value = "SN"; cells[0, 4].Value = "工序名称"; cells[0, 5].Value = "工序开始时间"; cells[0, 6].Value = "工序完成时间"; cells[0, 7].Value = "实际工时(分钟)"; cells[0, 8].Value = "参考工时(分钟)"; cells[0, 9].Value = "工效"; cells[0, 10].Value = "工序是否合格"; for (int i = 0; i < 11; i++) { if (i == 5 || i == 6) { cells.SetColumnWidth(i, 23); } else { cells.SetColumnWidth(i, 18); } } int index; for (index = 0; index < listOsptu.Count; index++) { cells[index + 1, 0].Value = index + 1; cells[index + 1, 1].Value = listOsptu[index].User_ID; cells[index + 1, 2].Value = listOsptu[index].Order_ID; cells[index + 1, 3].Value = listOsptu[index].SN; cells[index + 1, 4].Value = listOsptu[index].Template_Process_Name; cells[index + 1, 5].Value = listOsptu[index].Process_Start_Time.ToString(); cells[index + 1, 6].Value = listOsptu[index].Process_End_Time.ToString(); cells[index + 1, 7].Value = listOsptu[index].Process_Work_Time.ToString("F"); cells[index + 1, 8].Value = listOsptu[index].Reference_Work_Time.ToString("F"); cells[index + 1, 9].Value = listOsptu[index].Work_Efficiency.ToString("F"); if (listOsptu[index].Whether_Qualified == 1) { cells[index + 1, 10].Value = "合格"; } else { cells[index + 1, 10].Value = "不合格"; } } if (this.Tb_UserID.Text != "") { cells[index + 2, 0].Value = "用户ID"; cells[index + 2, 1].Value = "工时总计(分钟)"; cells[index + 2, 2].Value = "综合工效"; cells[index + 2, 3].Value = "工序合格率"; cells[index + 3, 0].Value = this.Tb_UserID.Text; cells[index + 3, 1].Value = totalWorkTime.ToString("F"); cells[index + 3, 2].Value = totalWorkEfficiency.ToString("F"); cells[index + 3, 3].Value = totalQualifiedRate.ToString("F"); } } wb.Save(src); Program.MF.Msg(GlobalConst.MSG_COLOR_DEFAULT, "下载完成！"); }
