c#中的excel文件格式不正确
问题描述:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Office.Interop.Excel;
namespace MMC_TA_TOOL
{
public class ExpotExcel
{
public void ExportToExcel(DataSet dataSet, string outputPath)
{
// Create the Excel Application object
Application excelApp = new Application();
// Create a new Excel Workbook
Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
int sheetIndex = 0;
// Copy each DataTable
foreach (System.Data.DataTable dt in dataSet.Tables)
{
// Copy the DataTable to an object array
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
// Copy the column names to the first row of the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
}
// Copy the values to the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
// 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);
// Create a new Sheet
Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName;
// Fast data export to Excel
string excelRange = string.Format("A1:{0}{1}",
finalColLetter, dt.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
// Mark the first row as BOLD
((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
}
// 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.SaveAs(Type.Missing, XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, XlSaveAsAccessMode.xlShared, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelWorkbook = null;
// Release the Application object
excelApp.Quit();
excelApp = null;
// Collect the unreferenced objects
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
i使用上面的代码来加载我的数据设置为excel但是在创建之后,当我试图打开时,除了它正在抛出文件格式不正确之后请帮助我。
i am using above code to load my data set to excel but after creation when i am trying to open that exce it is throwing that file format is not correct plase help me on this.
答
尝试将其保存为一个.XLS文件而不是.XLSX文件 - 它应该打开。毕竟你已经指定了XlWorkbookNormal格式。
(如果你想要XLSX,你需要XlFileFormat.xlWorkbookDefault)
错字:XSL for XLS - OriginalGriff [/ edit]
Try saving it as a .XLS file instead of a .XLSX file - it should open then. You have specified XlWorkbookNormal format after all.
(If you want XLSX, you need XlFileFormat.xlWorkbookDefault instead)
[edit]Typo: XSL for XLS - OriginalGriff[/edit]