【机房重构】C#导出到Excel

引言

    在机房重构中很多地方都用到了导出Excel，于是封装了一个类，直接去调用即可，这样简单方便了很多，同时也省去了很多的代码。下面用一个demo来实现导出到Excel

实现过程

1、添加引用

    Microsoft Office 15.0 Object Library,Microsoft Excel 15.0 Object Library,如下图

2、创建一个Excel工作簿

   

    Interop.Excel.Application xlApp = new Interop.Excel.Application(); 建立一个Excel.Application的新进程

    Excel.Workbooks workbooks = xlApp.Workbooks; //工作薄的创建

    Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); //工作表sheet的创建
    
    //也可以用下面的代码创建工作表sheet
    object missing = System.Reflection.Missing.Value;

    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet

3、插入数据

   

    object[,] dataArray = new object[rowCount, colCount];

    for (int j = 0; j < rowCount; j++)
    {
        for (int k = 0; k < colCount; k++)
        {
            dataArray[j, k] = drs[j][k].ToString();
        }
    }

    worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowCount, colCount]).Value2 = dataArray;

    注：这种方式是把一个二维数组的数据插入到Excel的固定区域，不用一个单元格一个单元格的插入，速度快很多，解决了导出数据慢的问题。

    可能你保存的时候定义Excel的格式，可以用NumberFormat来定义，例如：

1. stry +worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[rowCount+1,colCount]).NumberFormat="@"; 
   

4、保存Excel

   

    workbook.SaveAs(strFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    workbook.Close(true, Type.Missing, Type.Missing);
    workbook = null;

   以上是最简洁的方式，不过没有解决Excel行数的限制，主要是逻辑问题了

   

    /// 

    /// 导出Excel文件
    /// 
    /// 要导入到Excel的数据
    public static string DataSetToExcel2(System.Data.DataSet ds)
    {
        string strFilePath = System.AppDomain.CurrentDomain.BaseDirectory + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";

        Interop.Excel.Application xlApp = new Interop.Excel.Application();
        Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
        Interop.Excel.Workbook workbook = workbooks.Add(Interop.Excel.XlWBATemplate.xlWBATWorksheet);

        try
        {
            if (ds.Tables.Count == 0)
            {
                return "没有数据导出";
            }

            int MaxRowCount = 60000; //一个sheet的最大行数限制，主要考虑到office2003的行数有限

            DataTable dt = ds.Tables[0];//数据源

            int rowCount = dt.Rows.Count;
            int colCount = dt.Columns.Count;

            if (rowCount > 0 && rowCount <= MaxRowCount)
            {
                object missing = System.Reflection.Missing.Value;
                Interop.Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet
                //Worksheet worksheet = (Worksheet)workbook.Worksheets[i];
                worksheet.Name = "sheet";


                object[,] dataArray = new object[rowCount + 1, colCount];
                //表头导出
                for (int j = 0; j < colCount; j++)
                {
                    dataArray[0, j] = dt.Columns[j].ColumnName.ToString();
                }
                //内容导出
                for (int j = 0; j < rowCount; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        dataArray[j + 1, k] = dt.Rows[j][k].ToString();
                    }
                }
                worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowCount + 1, colCount]).NumberFormat = "@";
                worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowCount + 1, colCount]).Value2 = dataArray;
            }
            else //超过sheet表单的就再创适sheet表单
            {
                int sheetCount = 1; //sheet表单个数
                if (rowCount % MaxRowCount == 0)
                {
                    sheetCount = rowCount / MaxRowCount;
                }
                else
                {
                    sheetCount = rowCount / MaxRowCount + 1;
                }

                int Flag = 1;
                for (var m = 0; m < sheetCount; m++)
                {
                    //添加一个sheet表单
                    object missing1 = System.Reflection.Missing.Value;
                    Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook.Worksheets.Add(missing1, missing1, missing1, missing1);//添加一个sheet

                    worksheet1.Name = "第" + (m + 1) + "页sheet";
                    //如果是最后一个sheet的话 并且最后一个sheet表单数据不等于60000
                    if (Flag == sheetCount && (rowCount % MaxRowCount != 0))
                    {
                        int newrowCount = rowCount - ((Flag - 1) * MaxRowCount); //最后一个sheet的数据
                        object[,] dataArray = new object[newrowCount + 1, colCount];

                        int RowIndex = 0;


                        for (int j = 0; j < colCount; j++)
                        {
                            dataArray[0, j] = dt.Columns[j].ColumnName.ToString();
                        }

                        int startIndex = (Flag - 1) * MaxRowCount;
                        for (int n = startIndex; n < startIndex + newrowCount; n++)
                        {
                            for (int t = 0; t < colCount; t++)
                            {
                                dataArray[RowIndex + 1, t] = dt.Rows[n][t].ToString();
                            }

                               RowIndex++;
                        }
                        worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[newrowCount + 1, colCount]).NumberFormat = "@";
                        worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[newrowCount + 1, colCount]).Value2 = dataArray;

                    }
                    else
                    {
                        object[,] dataArray = new object[MaxRowCount + 1, colCount];

                        int RowIndex = 0;
                        for (int j = 0; j < colCount; j++)
                        {
                            dataArray[0, j] = dt.Columns[j].ColumnName.ToString();
                        }

                        int startIndex = (Flag - 1) * MaxRowCount;
                        for (int n = startIndex; n < startIndex + MaxRowCount; n++)
                        {
                            for (int t = 0; t < colCount; t++)
                            {
                                dataArray[RowIndex + 1, t] = dt.Rows[n][t].ToString();
                            }
                            if (n % 100 == 0)
                            {

                                System.Windows.Forms.Application.DoEvents();
                            }

                            RowIndex++;
                        }
                        worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[MaxRowCount + 1, colCount]).NumberFormat = "@";
                        worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[MaxRowCount + 1, colCount]).Value2 = dataArray;
                    }
                    Flag++;
                }

            }
            workbook.SaveAs(strFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            workbook.Close(true, Type.Missing, Type.Missing);
            workbook = null;
        }


        catch
        {
            xlApp.Quit();
            xlApp = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

            GC.Collect();
            return "出现异常";
        }
        finally
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

            GC.Collect();
        }

        return "导出成功，导出文件路径：" + strFilePath;
    }

结语

    导出到Excel的方法有很多，上面小编写的只是其中一种，仅供参考。这个实现其实很简单，理解原理就容易多了。遇到问题多去探索探索，这样会学到很多东西