EXCEL

在EXCEL中一个工作表只能存储65536行数据和256列。

该代码片段实现，一个DataTable存储多个工作表功能。

 

        /// <summary>
        /// 导出Excel
        /// 添加人员:阿朝
        /// 添加日期:20091024
        /// </summary>
        /// <param name="dataTable">数据源DataTable</param>
        /// <param name="outputPath">导出路径</param>
        /// <param name="excelSheetName">Sheet名称</param>
        /// <param name="isclu">是否存储</param>
        /// <param name="sheetIndex">工作表的索引</param>
        public static void ToLocalExcel(System.Data.DataTable dataTable, string outputPath, string excelSheetName,bool isclu,int sheetIndex)
        {
            sheetIndex = 0;
            sheetName = excelSheetName;
            try
            {
                    System.IO.File.Delete(outputPath);
                // Create the Excel Application object
                ApplicationClass excelApp = new ApplicationClass();

                // Create a new Excel Workbook
                Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
                ExcelDisposal(dataTable, excelApp, excelWorkbook);
                //excelApp.Application.AlertBeforeOverwriting = false;
                excelApp.Application.DisplayAlerts = false;
                // Save and Close the Workbook
                excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
                  Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
                  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excelWorkbook.Close(true, Type.Missing, Type.Missing);
                excelWorkbook = null;
               
                if (isclu)
                {
                    if (MessageBox.Show("是否打开?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        == DialogResult.Yes)
                    {
                        excelApp.Workbooks.Open(outputPath, Type.Missing,
                            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        excelApp.Visible = true;
                    }
                    else
                    {
                        // Release the Application object
                        excelApp.Quit();
                    }
                    MessageBox.Show("导出成功! ", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {// Release the Application object
                    excelApp.Quit();
                }
            }
            catch (IOException es)
            {
                throw new IOException("文件正在使用无法覆盖,请先关闭文件后覆盖!");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Collect the unreferenced objects
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        #region 私有方法 Excel 处理核心代码

        /// <summary>
        /// Excel处理
        /// </summary>
        /// <param name="dt"></param>
        private static void ExcelDisposal(System.Data.DataTable dt, ApplicationClass excelApp, Workbook excelWorkbook)
        {
            List<object[,]> list = new List<object[,]>();
            if (dt.Rows.Count > 65536)
            {
                for (int i = 0; i < dt.Rows.Count / 65535; i++)
                {
                    object[,] rawData = new object[65535 + 1, dt.Columns.Count];
                    list.Add(rawData);
                }
                if (dt.Rows.Count % 65535 != 0)
                {
                    object[,] rawData = new object[(dt.Rows.Count % 65535 + 1), dt.Columns.Count];
                    list.Add(rawData);
                }
            }
            else
            {
                // Copy the DataTable to an object array
                object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
                list.Add(rawData);
            }

            // Copy the column names to the first row of the object array
            foreach (object[,] rawDataNew in list)
            {
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    rawDataNew[0, col] = dt.Columns[col].ColumnName;
                }
            }
            int rowNew = 0;
            int row = 0;
            int b=0;
            object[,] rawDataObj = null;
            int sy = 0;
            // Copy the values to the object array
            try
            {
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    for (row = 0; row < dt.Rows.Count; row++)
                    {
                        for (; b < list.Count; )
                        {
                            if (rowNew % 65535 == 0 && rowNew != 0)
                            {
                                b++;
                                rawDataObj = list[b];
                                rowNew = 0;
                                rawDataObj[rowNew + 1, col] = dt.Rows[row].ItemArray[col];
                                break;
                            }
                            else
                            {
                                rawDataObj = list[b];
                                rawDataObj[rowNew + 1, col] = dt.Rows[row].ItemArray[col];
                                break;
                            }
                        }
                        rowNew++;
                    }
                    rowNew = 0;
                    b = 0;
                }
            }catch(Exception ex)
            {
           
            }
            // Calculate the final column letter
            string finalColLetter = string.Empty;
            string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            int colCharsetLen = colCharset.Length;

            if (dt.Columns.Count > colCharsetLen)
            {
                finalColLetter = colCharset.Substring(
                  (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
            }

            finalColLetter += colCharset.Substring(
                (dt.Columns.Count - 1) % colCharsetLen, 1);
            sheetIndex = 0;
            // Create a new Sheet
            int countRowcount = dt.Rows.Count;
            for (int k = 0; k < list.Count; k++)
            {
                Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
                  excelWorkbook.Sheets.get_Item(++sheetIndex),
                  Type.Missing, 1, XlSheetType.xlWorksheet);

                if (sheetName != null && !sheetName.Equals(""))
                {
                    excelSheet.Name = sheetName + "_" + k;
                }
                else
                {
                    excelSheet.Name = dt.TableName + "_" + k; ;
                }
                // Mark the first row as BOLD
                ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
                ((Range)excelSheet.Rows[1, Type.Missing]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
                ((Range)excelSheet.Rows[1, Type.Missing]).VerticalAlignment = XlVAlign.xlVAlignCenter;

                // Fast data export to Excel
                string excelRange = "";
                if (k < list.Count - 1)
                {
                    excelRange = string.Format("A1:{0}{1}",
                  finalColLetter, 65535 + 1);// dt.Rows.Count + 1);
                }
                else
                {
                    excelRange = string.Format("A1:{0}{1}",
            finalColLetter, countRowcount % 65535 + 1);
                }
                if (k < list.Count - 1)
                {
                    rowCount = 65535 + 1;
                }
                else
                {
                    rowCount = countRowcount % 65535 + 1;
                }
                cluCount = dt.Columns.Count;
                object cell1 = excelSheet.Cells[1, 1];
                object cell2 = null;
                try
                {
                    cell2 = excelSheet.Cells[rowCount, cluCount];
                }
                catch (Exception ex)
                {
                }
                object falg = true;
                excelSheet.get_Range(cell1, cell2).Borders.LineStyle = falg;
                excelSheet.get_Range(cell1, cell2).Columns.AutoFit();
                excelSheet.get_Range(cell1, cell2).Rows.AutoFit();
                //excelApp.ActiveWindow.DisplayGridlines = false;
                object size = 10;
                excelSheet.get_Range(cell1, cell2).Font.Size = size;

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].DataType == typeof(string))
                    {
                        cell1 = excelSheet.Cells[2, i + 1];
                        cell2 = excelSheet.Cells[rowCount, i + 1];
                        excelSheet.get_Range(cell1, cell2).NumberFormat = "@";
                    }
                }
                excelSheet.get_Range(excelRange, Type.Missing).Value2 =list[k]; //rawData;
                cell1 = excelSheet.Cells[1, 1];
                cell2 = excelSheet.Cells[rowCount, cluCount];
                excelSheet.get_Range(cell1, cell2).Columns.AutoFit();
                excelSheet.get_Range(cell1, cell2).Rows.AutoFit();
                //excelSheet.get_Range(cell1, cell2).HorizontalAlignment = XlHAlign.xlHAlignLeft;
                //excelSheet.get_Range(cell1, cell2).VerticalAlignment = XlVAlign.xlVAlignJustify;
            }
        }

        #endregion