导入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, "下载完成!");
        }

 

posted on 2020-11-19 17:27  Manuel  阅读(52)  评论(0编辑  收藏  举报