C#导出EXCEL，并生成charts表

需要添加引用 Microsoft.Office.Interop.Excel

注意：使用Microsoft.Office.Interop.Excel 非常耗时。对性能有要求建议用其他。

如果要用，把数据转成字符串拷贝到剪贴板中，然后把剪贴板数据粘贴到sheet表中即可，几十万数据秒级。下面有讲到怎么转换字符串拷贝与粘贴。

代码部分

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

/******************************************************************* 

* Copyright (C)  版权所有

* 文件名称:ExcelManage

* 命名空间:WindowsFormsApplication3

* 创建时间:2019/3/4 15:20:27

* 作    者: wangyonglai

* 描    述：

* 修改记录:

* 修改人:

* 版 本 号:v1.0.0

**********************************************************************/

namespace WindowsFormsApplication3

{

    //替代名称

    using Excel = Microsoft.Office.Interop.Excel;//替代名称

    using Missing = System.Reflection.Missing;

    class ExcelManage

    {

        public System.Data.DataSet dataSet = new System.Data.DataSet();

        public void InitalTable()

        {

            System.Data.DataTable table = new System.Data.DataTable();

            table.Columns.Add("序号", typeof(int));

            table.Columns.Add("数据1", typeof(int));

            table.Columns.Add("数据2", typeof(int));

            Random r = new Random();

            for (int i = 0; i < 200; i++)

            {

                if (i == 6 || i == 16) continue;

                table.Rows.Add(i + 1, r.Next(50), r.Next(60));

            }

            dataSet.Tables.Add(table);

            //ExportExcel(dataSet);

        }

        public void ExportExcel(System.Data.DataSet ds)

        {

            //新建EXCEL应用

            Excel.Application excelApp = new Excel.Application();

            if (excelApp == null)

                return;

            //设置为不可见，操作在后台执行，为 true 的话会打开 Excel

            excelApp.Visible = false;

            //初始化工作簿

            Excel.Workbooks workbooks = excelApp.Workbooks;

            //新增加一个工作簿，Add（）方法也可以直接传入参数 true

            //Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

            //同样是新增一个工作簿，但是会弹出保存对话框

            Excel.Workbook workbook = workbooks.Add(true);

            //Excel.Worksheet c_worksheet = workbook.Worksheets[1];

            //int a = workbook.Worksheets.Count;

            //workbook.Sheets.Add(Missing.Value, workbook.Sheets[1], ds.Tables.Count - 1, Missing.Value);

            for (int index = 0; index < ds.Tables.Count; index++)

            {

                System.Data.DataTable dt = ds.Tables[index];

                Excel.Worksheet worksheet = workbook.Worksheets.Add();

                //Excel.Worksheet worksheet = workbook.Worksheets[index + 1];

                //创建一个单元格

                Excel.Range range;

                int rowIndex = 1;       //行的起始下标为 1

                int colIndex = 1;       //列的起始下标为 1

                for (int i = 0; i < dt.Columns.Count; i++)

                {

                    //设置第一行，即列名

                    worksheet.Cells[rowIndex, colIndex + i] = dt.Columns[i].ColumnName;

                    //获取第一行的每个单元格

                    range = worksheet.Cells[rowIndex, colIndex + i];

                    //字体加粗

                    range.Font.Bold = true;

                    //设置为黑色

                    range.Font.Color = 0;

                    //设置为宋体

                    range.Font.Name = "Arial";

                    //设置字体大小

                    range.Font.Size = 12;

                    //水平居中

                    range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                    //垂直居中

                    range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                }

                //跳过第一行，第一行写入了列名

                rowIndex++;

                //写入数据

                for (int i = 0; i < dt.Rows.Count; i++)

                {

                    for (int j = 0; j < dt.Columns.Count; j++)

                    {

                        worksheet.Cells[rowIndex + i, colIndex + j] = dt.Rows[i][j].ToString();

                        range = worksheet.Cells[rowIndex + i, colIndex + j];

                        range.Interior.Color = System.Drawing.Color.Yellow;

                        range.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

                        range.Borders.Weight = Excel.XlBorderWeight.xlHairline;//边框常规粗细

                    }

                }

                //设置所有单元格列宽为自动列宽

                worksheet.Cells.Columns.AutoFit();

                #region 冻结行

                worksheet.Select();

                excelApp.ActiveWindow.SplitRow = 1;

                excelApp.ActiveWindow.SplitColumn = 0;

                excelApp.ActiveWindow.FreezePanes = true;

                #endregion

                #region 合并行

                //Excel.Range mergeRange = worksheet.get_Range("A25", "B25");

                //mergeRange.Merge();

                /////合并单元格之后，设置其中的文本

                //mergeRange.Value = "mergeRange";

                #endregion

                #region 绘制CHART图表

                int num = dt.Rows.Count + 1;

                Excel.Chart xlChart = workbook.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value);

                Excel.Range ranges1 = worksheet.Cells[1, 1];

                Excel.Range ranges2 = worksheet.Cells[num, dt.Columns.Count];

                Excel.Range chartRage = worksheet.get_Range(ranges1, ranges2);

                xlChart.ChartWizard(chartRage, Excel.XlChartType.xlLine, Missing.Value,

                                    Excel.XlRowCol.xlColumns, 1, 1,

                                    Missing.Value, Missing.Value, "X值", "Y值", Missing.Value);

                workbook.ActiveChart.HasTitle = true;

                workbook.ActiveChart.ChartTitle.Text = "图表名称";

                workbook.ActiveChart.HasDataTable = false;

                //给图表放在指定位置

                //workbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, worksheet.Name);

                xlChart.Name = "CC1";

                #endregion

                //workbook.Worksheets.Item(

            }

            //是否提示，如果想删除某个sheet页，首先要将此项设为fasle。

            excelApp.DisplayAlerts = false;

            //保存写入的数据，这里还没有保存到磁盘

            workbook.Saved = true;

            workbook.SaveCopyAs(@"C:\Users\Lenovo\Desktop\sample.xlsx");

            workbook.Close();

            excelApp.Quit();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);

            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

            workbook = null;

            //worksheet = null;

            //shapeSheet = null;

            excelApp = null;

            GC.Collect();

        }

    }

}

效果

最后由于此方法写入时非常耗时，我们可以采用先把数据转成字符串拷贝到剪贴板中，然后在复制到sheet表中，这样十万数据只要几秒钟

代码

public void ExportExcel()

        {

            System.Data.DataTable dt = new System.Data.DataTable();

            dt.Columns.Add("序号", typeof(int));

            dt.Columns.Add("数据1", typeof(int));

            dt.Columns.Add("数据2", typeof(int));

            Random r = new Random();

            for (int i = 0; i < 20000; i++)

            {

                if (i == 6 || i == 16) continue;

                dt.Rows.Add(i + 1, r.Next(50), r.Next(60));

            }

            StringBuilder strbu = new StringBuilder();

            //写入标题

            for (int i = 0; i < dt.Columns.Count; i++)

            {

                strbu.Append(dt.Columns[i].ColumnName.ToString() + "\t");

            }

            //加入换行字符串

            strbu.Append(Environment.NewLine);

            //写入内容

            for (int i = 0; i < dt.Rows.Count; i++)

            {

                for (int j = 0; j < dt.Columns.Count; j++)

                {

                    strbu.Append(dt.Rows[i][j].ToString() + "\t");

                }

                strbu.Append(Environment.NewLine);

            }

            System.Windows.Forms.Clipboard.SetText(strbu.ToString());

            //新建EXCEL应用

            Excel.Application excelApp = new Excel.Application();

            if (excelApp == null)

                return;

            //设置为不可见，操作在后台执行，为 true 的话会打开 Excel

            excelApp.Visible = false;

            //初始化工作簿

            Excel.Workbooks workbooks = excelApp.Workbooks;

            //新增加一个工作簿，Add（）方法也可以直接传入参数 true

            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

            //同样是新增一个工作簿，但是会弹出保存对话框

            //Excel.Workbook workbook = workbooks.Add(true);

            //Excel.Worksheet worksheet = workbook.Worksheets[1];

            Excel.Worksheet worksheet = workbook.Worksheets.Add();

            //Excel.Range ranges1 = worksheet.Cells[1, 1];

            //Excel.Range ranges2 = worksheet.Cells[dt.Rows.Count + 1, dt.Columns.Count];

            //Excel.Range chartRage = worksheet.get_Range(ranges1, ranges2);

            //Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[dt.Rows.Count+1, dt.Columns.Count]);

            //chartRage.Copy(strbu.ToString());

            //ranges1.Value = System.Windows.Forms.Clipboard.GetText();

            //worksheet.Activate();

            worksheet.Paste();

            //worksheet.PasteSpecial(System.Windows.Forms.Clipboard.GetText(), false, false);

            //新建一个 Excel 文件

            string filePath = @"C:\Users\Lenovo\Desktop\" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx";

            //创建文件

            FileStream file = new FileStream(filePath, FileMode.CreateNew);

            //关闭释放流，不然没办法写入数据

            file.Close();

            file.Dispose();

            //保存写入的数据，这里还没有保存到磁盘

            workbook.Saved = true;

            //保存到指定的路径

            workbook.SaveCopyAs(filePath);

        }

 private void WriteDataToExcel

{

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

            DataTable dataTable1 = this.GetTabel1();//获取表格2

            Microsoft.Office.Interop.Excel.Application excelApp;

            Microsoft.Office.Interop.Excel._Workbook workBook;

            Microsoft.Office.Interop.Excel._Worksheet workSheet;

            Microsoft.Office.Interop.Excel._Worksheet workSheet1;

            object misValue = System.Reflection.Missing.Value;

            workBook = excelApp.Workbooks.Add(misValue);//加载模型

            workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.Sheets.get_Item();//第一个工作薄。

            workSheet1 = (Microsoft.Office.Interop.Excel._Worksheet)workBook.Sheets.get_Item();

            int rowIndex = ;

            int colIndex = ;

            foreach (DataRow row in dataTable.Rows)

            {

                rowIndex++;

                colIndex = ;

                foreach (DataColumn col in dataTable.Columns)

                {

                    colIndex++;

                    workSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();

                }

            }

            rowIndex = ;

            colIndex = ;

            foreach (DataRow row in dataTable1.Rows)

            {

                rowIndex++;

                colIndex = ;

                foreach (DataColumn col in dataTable1.Columns)

                {

                    colIndex++;

                    workSheet1.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();

                }

            }

　　　　　　　　

 　　　　　　 workSheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,

                              Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                              Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                              Type.Missing, true, Type.Missing, Type.Missing);

            //保护工作表

            workSheet1.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,

                              Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                              Type.Missing, Type.Missing, Type.Missing, Type.Missing,

                              Type.Missing, true, Type.Missing, Type.Missing);

            /**/

            excelApp.Visible = false;

            workBook.SaveAs(@"D:\outputFormDataBase1.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,

                misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,

                misValue, misValue, misValue, misValue, misValue);

            dataTable = null;

            workBook.Close(true, misValue, misValue);

            excelApp.Quit();

            PublicMethod.Kill(excelApp);//调用kill当前excel进程  

}

有兴趣的可以看看 https://www.cnblogs.com/junshijie/p/5292087.html 这篇文章，里面有更详细如何操作EXCEL。

巴特西

C#导出EXCEL，并生成charts表

需要添加引用 Microsoft.Office.Interop.Excel

代码部分

效果

最后由于此方法写入时非常耗时，我们可以采用先把数据转成字符串拷贝到剪贴板中，然后在复制到sheet表中，这样十万数据只要几秒钟

最新文章

热门文章