C# 导出到Excel （有模板、分页）

把数据导出到Excel以供打印有很多种方式，大概有5种，这里不多说。同时也有很多要求，如

一 要求：导出Excel 格式如下：        

          （1）要分页，即n个表显示大量数据，并且每个表都要有表头和列名

          （2）列名要有单位

          （3）边框样式：内边框细实线，外边框粗实线

          （4）各行数据要适应数据宽度，所有列要打印到一个页面上

          （5）不能有空行边框

二 解决方法：调用com组件、使用模板的方式

   *优点：因为模板的格式相对固定，有表头样式、规定了必要的字段、某字段格式如日期、列名有单位、边框样式、列

           名适应打印出的数据宽度

 

   *需解决的问题：

           1 dataTable表字段与模板Excel字段名不一致，dataTable表字段可能没有单位， 而模板Excel字段要有单位

           2 dataTable表字段数目不定、顺序不定（由于页面上有“自定义显示”设置，用户可任意选择字段、调整顺序），      

             但要和模板Excel字段列数和列的顺序分别相对应 

三 代码

(1)1.1横向分页，即n个Sheet，n个表

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Data;

 

public class CommonToExcel
        {

            protected string outputFile = null;

            protected object missing = Missing.Value;

            DateTime beforeTime;

            DateTime afterTime;

            private static ArrayList cols = null;  //存储WorkSheet对象的所有列名
            private static Dictionary<string, string> Dic = null;

            private DataTable myDataTable;
            /// <summary>
            /// DataTable
            /// </summary>
            public DataTable MyDataTable
            {
                get { return myDataTable; }
                set { myDataTable = value; }
            }

            /// <summary>
            /// 将DataTable数据写入Excel文件（套用模板并分页）
            /// </summary>
            /// <param name="outputFile">输出文件名</param>
            /// <param name="rows">每个WorkSheet写入多少行数据</param>
            /// <param name="top">行索引</param>
            /// <param name="left">列索引</param>
            /// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2”</param>
            public bool ExcelCopy(string outputFile, int rows, int top, int left, string sheetPrefixName)
            {
                Excel._Application App;
                Excel._Workbook workBook;    //模板
                bool Isopen;

                if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
                    sheetPrefixName = "Sheet";
               

                try
                {

                    beforeTime = DateTime.Now;

                    GetWorkbook(outputFile, out App, out workBook, out Isopen);

                    if (workBook == null) { return false; }
                    //得到WorkSheet对象
                    Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

                    MatchCols(myDataTable, workSheet);

                    TablToExcel(myDataTable, rows, top, left, sheetPrefixName, workBook);

                    Kill(ref App, ref workBook, ref workSheet);

                    return true;
                }
                catch
                { return false; }

            }

 

 

            /// <summary>

            /// 获取WorkSheet数量

            /// </summary>

            /// <param name="rowCount">记录总行数</param>

            /// <param name="rows">每WorkSheet行数</param>

            private int GetSheetCount(int rowCount, int rows)
            {

                int n = rowCount % rows;        //余数

 

                if (n == 0)

                    return rowCount / rows;

                else

                    return Convert.ToInt32(rowCount / rows) + 1;

            }

            private void GetWorkbook(string filePatch, out Excel._Application App, out Excel._Workbook workbook, out bool Isopen)
            {

                Isopen = IsOpen(filePatch);

                if (Isopen == true)
                {
                    workbook = GetWorkbook(out App, filePatch, "");
                }
                else
                {
                    //创建一个Application对象,默认不可见
                    App = new Excel.Application();
                    //App.Visible = true;
                    afterTime = DateTime.Now;
                    //打开文件，得到WorkBook对象
                    workbook = OpenWorkbook(App, filePatch, "");
                }

            }

            private bool IsOpen(string patch)
            {
                bool fileopen = false;
                try
                {
                    System.IO.File.Move(patch, patch);
                }
                catch
                {
                    fileopen = true;
                }
                return fileopen;
            }

            private Excel._Workbook GetWorkbook(out Excel._Application App, string patch, string Password)
            {
                Excel._Workbook templetWorkbook = null;
                Excel._Workbook tempbook = null;
                int iTries = 0;

            TryAgain:
                try
                {
                    //templetWorkbook = System.Runtime.InteropServices.Marshal.BindToMoniker(patch) as Excel._Workbook;
                    App = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

                    afterTime = DateTime.Now;

                    int x = App.Workbooks.Count;
                    for (int i = 0; i < x; i++)
                    {
                        tempbook = App.Workbooks.get_Item(i + 1);
                        if (tempbook.FullName.ToUpper() == patch.ToUpper())
                        {
                            templetWorkbook = tempbook;
                            break;
                        }
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }
                if (templetWorkbook == null)
                {
                    iTries++;
                    if (iTries < 5)
                    {
                        System.Threading.Thread.Sleep(500); // Wait 1/2 seconds.

                        goto TryAgain; //resume code at the GetObject line
                    }
                    else
                    {
                        MessageBox.Show("Excel模板文件打开错误，请关闭正在使用的Excel文件后重试！");
                    }
                }
                return templetWorkbook;
            }

            private Excel._Workbook OpenWorkbook(Excel._Application App, string patch, string Password)
            {
                object m = Type.Missing;
                Excel.Workbook templetWorkbook;
                if (Password == "")
                {
                    templetWorkbook = App.Workbooks.Open(patch, m, m, m, m, m, m, m, m, m, m, m, m, m, m);
                }
                else
                {
                    templetWorkbook = App.Workbooks.Open(patch, m, m, m, Password, m, m, m, m, m, m, m, m, m, m);
                }

                return templetWorkbook;
            }

 

            private void TablToExcel(DataTable myDataTable, int rows, int top, int left, string sheetPrefixName, Excel._Workbook workBook)
            {
                int rowCount = myDataTable.Rows.Count;        //源DataTable行数

                int colCount = myDataTable.Columns.Count;    //源DataTable列数

                int sheetCount = this.GetSheetCount(rowCount, rows);    //WorkSheet个数

 

                //复制sheetCount-1个WorkSheet对象

                for (int i = 1; i < sheetCount; i++)
                {

                    ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);

                }

 

                //将源DataTable数据写入Excel
                #region 将源DataTable数据写入Excel
                try
                {
                    for (int i = 1; i <= sheetCount; i++)
                    {

                        int startRow = (i - 1) * rows;        //记录起始行索引

                        int endRow = i * rows;            //记录结束行索引

 

                        //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数

                        if (i == sheetCount)
                            endRow = rowCount;

 

                        //获取要写入数据的WorkSheet对象，并重命名

                        Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                        sheet.Name = sheetPrefixName + "-" + i.ToString();

 

                        string temp;
                        string ExCol;
                        int j = 0;
                        //将myDataTable中的数据写入WorkSheet

                        for (j = 0; j < endRow - startRow; j++)     //循环每页的行
                        {
                            for (int k = 0; k < cols.Count; k++)
                            {

                                ExCol = cols[k].ToString();                //Excel里的

                                if (Dic.ContainsKey(ExCol))
                                {
                                    string va = Dic[ExCol].ToString();

                                    temp = myDataTable.Rows[startRow + j][va].ToString();

                                    sheet.Cells[top + j, left + k] = temp;
                                }

                            }

                        }

                       

                        if (i == sheetCount)  //设置最后一页的格式
                        {
                            Excel.Range excelRange = sheet.get_Range(sheet.Cells[3, 1], sheet.Cells[top - 1 + j, cols.Count]);
                            excelRange.Borders.LineStyle = 1;
                            excelRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, null);

                            Excel.Range excelRange1 = sheet.get_Range(sheet.Cells[top + j, 1], sheet.Cells[top + 25, cols.Count]);
                            excelRange1.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;

                        }

                        sheet.PageSetup.CenterFooter = string.Format("第{0}页，共{1}页", i, sheetCount);
                    }
                }
                catch (Exception ex)
                {
                    //Log.WriteFile("TablToExcel.Msg=" + ex.Message, "log.ini");
                }
                #endregion
            }

            private static void MatchCols(DataTable myDataTable, Excel.Worksheet workSheet)
            {
               
                string cont;
                cols = new ArrayList();

                try
                {
                    //得到WorkSheet对象的所有列名，cols存
                    int n = 1;
                    while (true)
                    {

                        cont = ((Excel.Range)workSheet.Cells[3, n]).Text.ToString();//第三行
                        if (n > 5 && cont == "") break;   //Excel表里列名为空 m>myDataTable.Columns.Count
                        cont = PartStr(cont);
                        if (cont != "")
                            cols.Add(cont);

                        n++;
                    }

                    //得到对应列的DataTable的列名，dic存
                    Dic = new Dictionary<string, string>();   //以去掉单位的列名为键，以全列名为值

                    for (int m = 0; m < myDataTable.Columns.Count; m++)
                    {
                       
                        string col = cols[m].ToString();

                        DataColumn dc = myDataTable.Columns[m];

                        string newName = PartStr(dc.ColumnName);

                        if (cols.Contains(newName))
                        {
                            Dic.Add(newName, dc.ColumnName);
                        }

                    }
                   
                }
                catch
                { }
            }

 

            /// <summary>
            /// 将字符串（列名）去掉单位
            /// </summary>
            /// <param name="cont"></param>
            /// <returns></returns>
            private static string PartStr(string cont)
            {
                StringBuilder sb = new StringBuilder();
                Regex regex = new Regex("[/u4e00-/u9fa5]+", RegexOptions.Compiled);
                char[] stringChar = cont.ToCharArray();
                for (int i = 0; i < stringChar.Length; i++)
                {
                    if (regex.IsMatch((stringChar[i]).ToString())) //if((int)q[i]>=0x4E00 && (int)q[i]<=0x9FA5)
                    {
                        sb.Append(stringChar[i]);
                    }
                }
                return sb.ToString();

                //    for (int i = 0; i < stringChar.Length; i++)
                //    {
                //        int code = Char.ConvertToUtf32(cont, i);
                //        if(code>=4E00 && code<=9FFF)
                //    }
               
            }

 

            private void Kill(ref Excel._Application App, ref Excel._Workbook workBook, ref Excel.Worksheet workSheet)
            {
                try
                {
                    workBook.Save();
                    //workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
                    //workBook.SaveAs("当前状态1.xls ", missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);

                    workBook.Close(null, null, null);

                    App.Workbooks.Close();

                    App.Application.Quit();

                    App.Quit();

 

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

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(App);

 

                    workSheet = null;
                    //fileworkSheet = null;

                    workBook = null;
                    //fileWorkbook = null;

                    App = null;

 

                    GC.Collect();

                }

                catch (Exception e)
                {

                    throw e;

                }

                finally
                {

                    Process[] myProcesses;

                    DateTime startTime;

                    myProcesses = Process.GetProcessesByName("Excel");

 

                    //得不到Excel进程ID，暂时只能判断进程启动时间

                    foreach (Process myProcess in myProcesses)
                    {

                        startTime = myProcess.StartTime;

 

                        if (startTime > beforeTime && startTime < afterTime)
                        {

                            myProcess.Kill();

                        }

                    }

                }
            }

            
        }

 1.2 纵向分页，即1个Sheet，n个表

      关键代码：

     private void TablToExcel(DataTable myDataTable, int rows, int top, int left)
            {
                int dataTop = 0;    //Excel每一页的空行

                int rowCount = myDataTable.Rows.Count;        //源DataTable行数

                int colCount = myDataTable.Columns.Count;    //源DataTable列数

                int sheetCount = this.GetSheetCount(rowCount, rows);    //WorkSheet个数

               

                string v = NumberToChar(cols.Count);

                Excel.Range templetRange = fileXlSheet.get_Range("A1", v + "30");
                //templetRange.Copy(missing);
               

                //将源DataTable数据写入Excel
                #region 将源DataTable数据写入Excel
                try
                {
                    for (int i = 1; i <= sheetCount; i++)
                    {

                        int startRow = (i - 1) * rows;        //记录起始行索引

                        int endRow = i * rows;            //记录结束行索引

 

                        //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
                        if (i == sheetCount)
                            endRow = rowCount;

                        dataTop = WhereIsNull(fileXlSheet,rows);
                        Excel.Range fileRange = fileXlSheet.get_Range("A" + dataTop.ToString(), v + (rows + 4 + dataTop).ToString());//(rows+5-1+dataTop)

                        templetRange.Copy(fileRange);
                        //fileRange._PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);                      
                       

                        string temp;
                        string ExCol;
                        int j = 0;
                        //将myDataTable中的数据写入WorkSheet
                        for (j = 0; j < endRow - startRow; j++)     //循环每页的行
                        {
                            for (int k = 0; k < cols.Count; k++)
                            {

                                ExCol = cols[k].ToString(); //cols[k].ToString();     //数据表里现有的

                                if (Dic.ContainsKey(ExCol))
                                {
                                    string va = Dic[ExCol].ToString();

                                    temp = myDataTable.Rows[startRow + j][va].ToString();

                                    fileXlSheet.Cells[top + dataTop + j, left + k] = temp;

                                }

                            }

                        }

                        if (i == sheetCount)  //设置最后一页的格式
                        {
                            Excel.Range excelRange = fileXlSheet.get_Range(fileXlSheet.Cells[dataTop + 2, 1], fileXlSheet.Cells[top - 1 + dataTop + j, cols.Count]);
                            excelRange.Borders.LineStyle = 1;
                            excelRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, null);

                            Excel.Range excelRange1 = fileXlSheet.get_Range(fileXlSheet.Cells[top + dataTop + j, 1], fileXlSheet.Cells[dataTop + rows + 4, cols.Count]);
                            excelRange1.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;

                        }

                       
                        //设置最后一行的格式
                        Excel.Range excelRange2 = fileXlSheet.get_Range(fileXlSheet.Cells[top + dataTop + j, 1], fileXlSheet.Cells[top + dataTop + j, cols.Count]);
                        excelRange2.Merge(missing);
                        excelRange2.Value2 = string.Format("第{0}页，共{1}页", i, sheetCount);

                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                    //Log.WriteFile("TablToExcel.Msg=" + ex.Message, "log.ini");
                }
                #endregion
            }

           

            ///
            /// 把1,2,3,...,35,36转换成A,B,C,...,Y,Z
            ///
            /// 要转换成字母的数字（数字范围在闭区间[1,26]）
            ///
            private string NumberToChar(int number)
            {
                string va = "";
                try
                {
                    if (1 <= number && 26 >= number)
                    {
                        int num = number + 64;
                        System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
                        byte[] btNumber = new byte[] { (byte)num };
                        va = asciiEncoding.GetString(btNumber);
                    }

                }
                catch
                {
                    //Log.WriteFile("NumberToChar.Msg=Excel列名数量过多", "log.ini");
                }

                return va;

            }

            private int WhereIsNull(Excel._Worksheet Sheet,int rows)
            {
                string temp;
                int j = 0;
                for (int i = 1; i < 65535; i++)
                {
                    temp = ((Excel.Range)Sheet.Cells[i, 1]).Text.ToString();
                    if (temp == "")
                    {
                        j = i;
                        break;
                    }
                    else
                    {
                        i += rows + 4; //rows+5-1
                    }
                }
                return j;
            }

(2)调用代码

            string Patch = Application.StartupPath + "//日报//";
            string templetFile = Patch + "出油_模板.xls";
            string outputFile = Patch + string.Format("{0:yyMMddHHmm}", DateTime.Now) + "出油.xls";

            if (System.IO.Directory.Exists(Patch) != true)   //"日报//"
                System.IO.Directory.CreateDirectory(Patch);
            if (!System.IO.File.Exists(templetFile))
            {
                MessageBox.Show("没有找到模板!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (!System.IO.File.Exists(outputFile))
            {
                try
                {
                    System.IO.File.Copy(templetFile, outputFile, false);
                }
                catch (Exception except)
                { MessageBox.Show("error", "error"); }
            }

            CommonToExcel toExcel = new CommonToExcel();

            toExcel.MyDataTable = myDataTable;

            if (toExcel.ExcelCopy(outputFile, 25, 4, 1, "sheet") == true)
            {
                MessageBox.Show("报表导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                

            }