C#实现传入SQL查的数据表（DataTable）进行导出EXCEL表格的功能

lucky.帅

于 2023-11-08 15:56:38 发布

阅读量428

点赞数

本文链接：https://blog.csdn.net/qq_64948696/article/details/134291697

版权

C# .NET Framework 专栏收录该内容

47 篇文章 3 订阅

订阅专栏

本文介绍了一个C#方法，使用System.IO和Microsoft.Office.Interop.Excel库将DataTable数据动态写入Excel文件，包括表头处理、数据填充和总计计算，最后保存并设置只读属性。

摘要由CSDN通过智能技术生成

using System.IO; 
public void DataToExcel(DataTable m_DataTable)
        {
            string fileName = DateTime.Now.ToString("yyyy-MM-dd");
            string saveFileName = "";
            System.Windows.Forms.SaveFileDialog saveDialog = new System.Windows.Forms.SaveFileDialog
            {
                DefaultExt = "xlsx",
                Filter = "Excel文件|*.xlsx",
                FileName = fileName
            };
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0)
                return;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                System.Windows.Forms.MessageBox.Show("无法创建Excel对象，您的电脑可能未完全安装Office", "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

            int car_no_num_no = 0;
            int total_weight_no = 0;
            int leather_weigh_no = 0;
            int extra_no = 0;
            int sweight_no = 0;
            int zong_no = 0;

            string[] num = package.getxml("system.xml", "Product/excel", "sql").Split(',');
            for (int i = 0; i < num.Length; i++)
            {
                //EXCEL表头数据循环
            }

            string[] num1 = package.getxml("system.xml", "Product/excel", "name").Split(',');

            for (int i = 0; i < num1.Length; i++)
            {
                //EXCEL表头名字循环
                worksheet.Cells[1, i + 1] = num1[i];
            }

            worksheet.Columns[1].NumberFormat = "0";

            for (int r = 0; r < m_DataTable.Rows.Count; r++)
            {
                for (int i = 0; i < m_DataTable.Columns.Count; i++)
                {
                   //EXCEL每一列循环
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Cells.Borders.LineStyle = 1;
            //合计
            worksheet.Cells[2 + m_DataTable.Rows.Count, 1] = "合计";
            worksheet.Cells[2 + m_DataTable.Rows.Count, car_no_num_no + 1] = m_DataTable.Rows.Count;
           //要合计的内容循环 展示在最后一行 列：
            if (zong_no != 0)
            {
                double zong_sum = 0;
                for (int i = 0; i < m_DataTable.Rows.Count; i++)
                {
                    zong_sum = zong_sum + worksheet.Cells[i + 2, zong_no + 1].value;
                }
                worksheet.Cells[2 + m_DataTable.Rows.Count, zong_no + 1] = zong_sum.ToString();
            }

            worksheet.Columns.EntireColumn.AutoFit();
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    //设置为只读
                    FileInfo fileInfo = new FileInfo(saveFileName);
                    fileInfo.IsReadOnly = true;
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show("导出文件时出错,文件可能正被使用！\n" + ex.Message, "", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
                }
            }
            MessageBox.Show("保存成功！" + fileName);
            xlApp.Quit();
            GC.Collect();
        }