C# 写Excel

1、引用 MicroSoft.Office.Interop.Excel  在

　　C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

　　及Office

　　C:\Windows\assembly\GAC_MSIL\office\15.0.0.0__71e9bce111e9429c\OFFICE.DLL

2、初始信息

            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            //初始化表信息
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            fileCounter = 1;
            if (xlApp == null)
            {
                return;
            }
            xlApp.Visible = true;

最后

xlApp.ActiveWorkbook.Close(true, AppDomain.CurrentDomain.BaseDirectory + "检查结果");
xlApp.Quit();

 

3、一些好用的方法

（1）处理sheet页

/// <summary>
        /// 增加Sheet页，调整sheet名
        /// </summary>
        /// <param name="xlApp"></param>
        private void AddSheetAndRenameSheet(Microsoft.Office.Interop.Excel.Application xlApp)
        {
            Workbooks workbooks = xlApp.Workbooks;
            Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            //如果不去刻意设置表顺序，打开后的Excel只有个Sheet1，这时取1号就是当前的Sheet，再增加Sheet后，会在Sheet1的前面插入。
            Worksheet worksheet4 = (Worksheet)workbook.Worksheets.get_Item(1);
            worksheet4.Name = "第四个表单";
            PrintHeader(worksheet4, GetModelTable());

            Worksheet worksheet3 = workbook.Worksheets.Add();
            worksheet3.Name = "第三个表单";
            PrintHeader(worksheet3, GetModelTable());

            Worksheet worksheet2 = workbook.Worksheets.Add();
            worksheet2.Name = "第二个表单";
            PrintHeader(worksheet2, GetModelTable2());

            Worksheet worksheet1 = workbook.Worksheets.Add();
            worksheet1.Name = "第一个表单";
            PrintHeader(worksheet1, GetModelTable3());
        }

（2）

冻结表头

/// <summary>
        /// 冻结表头
        /// </summary>
        /// <param name="xlApp"></param>
        private void FrozenHeader(Microsoft.Office.Interop.Excel.Application xlApp)
        {
            foreach (Worksheet workSheet in xlApp.ActiveWorkbook.Worksheets)
            {
                workSheet.Activate();
                xlApp.ActiveWindow.SplitRow = workSheet.UsedRange.CurrentRegion.Rows.Count;//当前已经用到的区域，通常只冻结第一行
                //xlApp.ActiveWindow.SplitColumn = workSheet.UsedRange.CurrentRegion.Columns.Count;//当前已经用到的区域，冻结列。 不常用

　　　　　　　　 　xlApp.ActiveWindow.FreezePanes = true; 

} }

 

（3）输出表头

　　　　  /// <summary>
        /// 打印表头
        /// </summary>
        private void PrintHeader(Worksheet worksheet1, System.Data.DataTable dtInput)
        {
            Range range1;
            //表头
            for (int i = 0; i < dtInput.Columns.Count; i++)
            {
                worksheet1.Cells[1, i + 1] = dtInput.Columns[i].ColumnName;

                range1 = (Range)worksheet1.Cells[1, i + 1];
                range1.Interior.ColorIndex = 15;
                range1.Font.Bold = true;
                range1.HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平居中  
                range1.ColumnWidth = 15;
            }
        }

（4）将内容写入到Excel中

        /// <summary>
        /// 将指定的内容写入到Excel中
        /// </summary>
        /// <param name="xlApp"></param>
        /// <param name="SheetIndex">从多少行开始写第一个表内数据</param>
        /// <param name="startIndex"></param>
        /// <param name="dt"></param>
        private int PrintContent(Microsoft.Office.Interop.Excel.Application xlApp, int SheetIndex, int startIndex, System.Data.DataTable dtInput)
        {
            Workbooks workbooks = xlApp.Workbooks;
            Workbook workbook = workbooks[1];
            Worksheet worksheet = workbook.Worksheets[SheetIndex];
            worksheet.Activate();
            //表内容
            for (int i = 0; i < dtInput.Rows.Count; i++)
            {
                ((Range)worksheet.Cells[startIndex + i + 2, 1]).Activate();//视角跟随
                for (int j = 0; j < dtInput.Columns.Count; j++)
                {
                    worksheet.Cells[startIndex + i + 1, j + 1] = dtInput.Rows[i][j].ToString();
                }
            }
            startIndex += dtInput.Rows.Count;
            return startIndex;
        }

（5）将数据按照表写入Excel（批量写）

        /// <summary>
        /// 将指定的内容写入到Excel中
        /// </summary>
        private int PrintContentWithBulk(Microsoft.Office.Interop.Excel.Application xlApp, int sheetIndex, int startIndex, System.Data.DataTable dtInput)
        {
            Workbooks workbooks = xlApp.Workbooks;
            Workbook workbook = workbooks[1];
            Worksheet worksheet = workbook.Worksheets[sheetIndex];
            worksheet.Activate();
　　　　　　　//在内存中构造一个数据块
            object[,] objData = new Object[dtInput.Rows.Count, dtInput.Columns.Count];
            for (int i = 0; i < dtInput.Rows.Count; i++)
                for (int j = 0; j < dtInput.Columns.Count; j++)
                    objData[i, j] = dtInput.Rows[i][j];
　　　　　　  //选中Excel中相同大小的一块数据
            Range r = worksheet.get_Range("A" + (1 + startIndex).ToString(), Missing.Value);
            r = r.get_Resize(dtInput.Rows.Count, dtInput.Columns.Count);
            r.Value = objData;

            startIndex += dtInput.Rows.Count;
            return startIndex;
        }

（7）如果要填写的记录数可能大于Excel能接受的数目

        int fileCounter;

        /// <summary>
        /// 写数据
        /// </summary>
        void WriteContent()
        {
            System.Data.DataTable dtAlls = GetData();

            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            //初始化表信息
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            fileCounter = 1;
            LineCounter = 0;
            if (xlApp == null)
            {
                return;
            }
            xlApp.Visible = true;
            AddSheetAndRenameSheet(xlApp);

            for (int i = 0; i < dtAlls.Count; i++)
            {
                if (LineCounter > 65000)//这里设置条目数接近Excel行数允许的最大值时，就创建第二个文档
                {
                    AddBorder(xlApp);
                    xlApp.ActiveWorkbook.Close(true, AppDomain.CurrentDomain.BaseDirectory + "XXX数据结果" + "(" + fileCounter.ToString() + ")");
                    xlApp.Quit();
                    fileCounter++;
                    xlApp = new Microsoft.Office.Interop.Excel.Application();
                    if (xlApp == null)
                    {
                        return;
                    }
                    xlApp.Visible = true;
                    AddSheetAndRenameSheet(xlApp);
                    LineCounter = 1;
                }
                else
                {//写入这个表的数据
                    lineCounter=PrintContent(xlApp, lineCounter,dt);
                }
            }
            AddBorder(xlApp);
            if (fileCounter == 1)
                xlApp.ActiveWorkbook.Close(true, AppDomain.CurrentDomain.BaseDirectory + "XXX数据结果");
            else
                xlApp.ActiveWorkbook.Close(true, AppDomain.CurrentDomain.BaseDirectory + "XXX数据结果" + "(" + fileCounter.ToString() + ")");
            xlApp.Quit();
        }

 （8）视角跟随

 ((Range)worksheet.Cells[startIndex + i + 2, 1]).Activate();//视角跟随

（9）加边框

        /// <summary>
        /// 给所有的表格加边框
        /// </summary>
        /// <param name="xlApp"></param>
        private void AddBorder(Microsoft.Office.Interop.Excel.Application xlApp)
        {
            foreach (Worksheet workSheet in xlApp.ActiveWorkbook.Worksheets)
            {
                int RowCount = workSheet.UsedRange.CurrentRegion.Rows.Count;
                int ColumnCount = workSheet.UsedRange.CurrentRegion.Columns.Count;

                Range range = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[RowCount, ColumnCount]];//设置边框

                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
            }
            
        }