在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