winform 导出datatable 到excel

做个备忘

 

        public void DataTabletoExcel(System.Data.DataTable headTable, System.Data.DataTable contentTable, string strFileName)
        {
            if (headTable == null || contentTable == null) return;

            int rowIndex = 1;
            int columnIndex = 0;

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xlApp.DefaultFilePath = "";
            xlApp.DisplayAlerts = true;
            xlApp.SheetsInNewWorkbook = 1;
            Workbook xlBook = xlApp.Workbooks.Add(true);

            xlBook = oneTableToExcel(xlBook, headTable, columnIndex, rowIndex, xlApp);
            rowIndex = 4;
            xlBook = oneTableToExcel(xlBook, contentTable, columnIndex, rowIndex, xlApp);

            //
            string ExcelOutPutPathString = System.Configuration.ConfigurationManager.AppSettings["ExcelOutPutPathString"];
            xlBook.SaveCopyAs(ExcelOutPutPathString+"\\账目明细" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
            //xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));
            // xlBook.SaveCopyAs(strFileName);    }
        }

        private Workbook oneTableToExcel(Workbook xlBook, System.Data.DataTable tmpDataTable, int columnIndex, int rowIndex, Microsoft.Office.Interop.Excel.Application xlApp)
        {
            int rowNum = tmpDataTable.Rows.Count;
            int columnNum = tmpDataTable.Columns.Count;
            //Workbook xlBook = xlApp.Workbooks.Add(true);        
            //将DataTable的列名导入Excel表第一行  
            foreach (DataColumn dc in tmpDataTable.Columns)
            {
                columnIndex++;
                xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
            }
            //将DataTable中的数据导入Excel中     
            for (int i = 0; i < rowNum; i++)
            {
                rowIndex++;
                columnIndex = 0;
                for (int j = 0; j < columnNum; j++)
                {
                    columnIndex++;
                    xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
                }
            }
            return xlBook;
 
        }