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

来源：互联网发布：网络视频管理服务器编辑：程序博客网时间：2024/05/23 22:08
主要方法如下：
        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;        }
阅读全文
0 0