C# 导出Excel 多个Sheet 和 单个Sheet

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/sun_zeliang/article/details/81076948

在工作中,会遇到这样的需求,选取某一年,导出选择年份的月度、季度或者年度报表,月度报表不可能产生12个单独的Excel,这样的话客户可能会和你拼命,将12个月份分为12个Sheet放在一个Excel里面,可能客户会请你吃饭。

目前的做法是根据选择的年份,将该年份所有的数据全部获取到,根据表的类型(几个Sheet)分为多个DataTable放到DataSet里面,接着根据DataSet.Tables的数量导出相应数量Sheet的Excel。

步骤一:查询数据(代码略)

步骤二:分解DataTable(代码段一)

步骤三:导出Excel(代码段二)

附:单Sheet导出Excel(代码段三)

代码段一:

        /// <summary>
        /// 将DataTable里面的数据按 type 规则分为一定数量的DataTable 放到DataSet里
        /// </summary>
        /// <param name="ds">目标存储</param>
        /// <param name="dt">源数据</param>
        /// <param name="type">规则类型</param>
        private void Classification(DataSet ds,DataTable dt, string type)
        {
            if (type == "月度报表")
            {
                for (int i = 0; i < 12; i++)
                {
                    DataTable table = new DataTable();
                    table = dt.Clone();
                    table.TableName = "dt" + i;
                    ds.Tables.Add(table);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < 12; j++)
                    {
                        if ((int)dt.Rows[i]["TB_I_Month"] == j + 1)
                        {
                            ds.Tables[j].Rows.Add(dt.Rows[i].ItemArray);
                            continue;
                        }
                    }
                }
                for (int i = 0; i < 12; i++)
                {
                    ds.Tables[i].Columns.Remove(ds.Tables[i].Columns["TB_I_Month"]);
                }
            }
            if (type == "季度报表")
            {
                for (int i = 0; i < 4; i++)
                {
                    DataTable table = new DataTable();
                    table = dt.Clone();
                    table.TableName = "dt" + i;
                    ds.Tables.Add(table);
                }

                int index = 0;
                for (int j = 0; j < 12; j += 3)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if ((int)dt.Rows[i]["TB_I_Month"] == j + 1 || (int)dt.Rows[i]    ["TB_I_Month"] == j + 2 || (int)dt.Rows[i]["TB_I_Month"] == j + 3)
                        {
                            ds.Tables[index].Rows.Add(dt.Rows[i].ItemArray);
                        }
                    }
                    index++;
                }
                for (int i = 0; i < 4; i++)
                {
                    ds.Tables[i].Columns.Remove(ds.Tables[i].Columns["TB_I_Month"]);
                }
            }
            if (type == "年度报表")
            {
                DataTable table = new DataTable();
                table = dt.Clone();
                table.TableName = "dt";
                ds.Tables.Add(table);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ds.Tables[0].Rows.Add(dt.Rows[i].ItemArray);
                }
                ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["TB_I_Month"]);
            }
        }

 代码段二:

        /// <summary>
        /// 导出多 Sheet 报表
        /// </summary>
        /// <param name="ds">要导出的数据</param>
        public void DataToExcle(System.Data.DataSet ds)
        {
            if (ds.Tables == null||ds.Tables.Count==0)
            {
                MessageBox.Show("无数据!");
                return;
            }
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.DefaultExt = "xlsx";
            saveFileDialog.Title = "导出数据到Execl表格";
            if (ds.Tables.Count == 12)
                saveFileDialog.FileName = "月度报表";
            if (ds.Tables.Count == 4)
                saveFileDialog.FileName = "季度报表";
            if (ds.Tables.Count == 1)
                saveFileDialog.FileName = "年度报表";
            saveFileDialog.Filter = "microsoft office execl files (*.xlsx)|*.xlsx";
            saveFileDialog.RestoreDirectory = true;

            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                System.Reflection.Missing miss = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
                xlapp.Visible = true;
                Workbooks workbooks = xlapp.Workbooks;
                Workbook workbook = workbooks.Add(true);
                if (ds.Tables.Count > 1)
                {
                    workbook.Sheets.Add(miss,workbook.Sheets[1], ds.Tables.Count - 1, miss);
                }
                
                for (int d = 0; d < ds.Tables.Count; d++)
                {
                    Worksheet ws = workbook.Worksheets[d + 1] as Worksheet;
                    if (ds.Tables.Count == 12)
                        ws.Name = d + 1 + "月份";
                    if (ds.Tables.Count == 4)
                        ws.Name = d + 1 + "季度";
                    if (ds.Tables.Count == 1)
                        ws.Name = "年度";
                    int totalCellsCount = ds.Tables[d].Columns.Count;
                    Range range = ws.Range[ws.Cells[1, 1], ws.Cells[1, ds.Tables[d].Columns.Count]];
                    //生成列
                    for (int i = 0; i < ds.Tables[d].Columns.Count; i++)
                    {
                        range.Cells[1, i + 1] = ds.Tables[d].Columns[i].ColumnName;
                        range.Interior.ColorIndex = 15;
                        range.Font.Bold = true;
                        range.Font.Size = 10;
                        range.EntireColumn.AutoFit();
                        range.EntireRow.AutoFit();
                    }

                    //插入数据
                    for (int r = 0; r < ds.Tables[d].Rows.Count; r++)
                    {
                        for (int i = 0; i < ds.Tables[d].Columns.Count; i++)
                        {
                            ws.Cells[r + 2, i + 1] = "'" + ds.Tables[d].Rows[r][i].ToString();
                        }
                    }
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                    range = null;
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
                    ws = null;
                }
                ((Worksheet)workbook.Sheets[1]).Select(true);//选中第一个worksheet
                workbook.Close(true, miss, miss);//关闭workbook, 并保存对workbook的所有修改

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                xlapp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp);
                xlapp = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

代码段三:

        /// <summary>
        /// 导出单Sheet 报表
        /// </summary>
        /// <param name="dt">数据</param>
        public void DataToExcle(System.Data.DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                MessageBox.Show("无数据!");
                return;
            }
            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.DefaultExt = "xlsx";
            saveFileDialog.Filter = "microsoft office execl files (*.xlsx)|*.xlsx";
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.Title = "导出数据到excel表格";
            saveFileDialog.FileName = "报表";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                System.Reflection.Missing miss = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
                xlapp.Visible = true;
                Workbooks workbooks = xlapp.Workbooks;
                Workbook workbook = workbooks.Add(true);

                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                Microsoft.Office.Interop.Excel.Range range;
                long totalCount = dt.Rows.Count;
                long totalcolumns = dt.Columns.Count;

                //生成列
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                    range.Interior.ColorIndex = 15;
                    range.Font.Bold = true;
                }
                //插入数据
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        worksheet.Cells[r + 2, i + 1] = "'" + dt.Rows[r][i].ToString();
                    }
                }
                ((Worksheet)workbook.Sheets[1]).Select(true);//选中第一个worksheet
                workbook.Close(true, miss, miss);//关闭workbook, 并保存对workbook的所有修改

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                xlapp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp);
                xlapp = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

新手写代码,遇大佬指点,不胜感激,感谢观看

展开阅读全文

没有更多推荐了,返回首页