使用C#及Office组件将DataTable对象保存为Excel文件

最新推荐文章于 2024-08-11 22:03:00 发布

acngiser

最新推荐文章于 2024-08-11 22:03:00 发布

阅读量823

点赞数

本文链接：https://blog.csdn.net/a_dev/article/details/77670864

版权

主要方法如下：

        private string ExportExcelFile(DataTable dt, string strFullPath)
        {
            string strReturnPath = "";
            try
            {
                if (dt == null || dt.Rows.Count == 0)
                {
                    return strReturnPath;
                }
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    return strReturnPath;
                }
                //xlApp.Visible = true; //可视化
                System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                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];
                Microsoft.Office.Interop.Excel.Range range;
                long totalCount = dt.Rows.Count;
                long rowRead = 0;
                float percent = 0;
                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;
                }
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        try
                        {
                            worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
                        }
                        catch
                        {
                            worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString().Replace("=", "");
                        }
                    }
                    rowRead++;
                    percent = ((float)(100 * rowRead)) / totalCount;
                }
                workbook.SaveAs(strFullPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                workbook.Close(true, Type.Missing, Type.Missing);
                xlApp.Quit();
                workbook = null;
                xlApp = null;
                GC.Collect();
                strReturnPath = strFullPath; //D:\\cache\\2017082910111919_testXls.xls
            }
            catch (Exception ex)
            {
                log.Info("保存Excel文件失败：" + ex.Message);
            }
            return strReturnPath;
        }