最近项目需要一个导出功能, 我一开始使用传统的直接写html到文件的方式, 但是发现着这种方法导出的Ecxel 文件在office 2010 中打开不正常, 然后只好采用这个种COM 的方式了。现将代码贴出来供大家参考。
期间你很可能遇到的异常(我都遇到过,使我放弃的心都有了), 解决方案也贴出来,让大家少苦逼一会:
1)Error accessing COM components
参考我的另一篇博客: http://blog.csdn.net/q573625392/article/details/8931597
2)Microsoft Office Excel cannot access the file 'XXXX'
参考我的另一篇博客: http://blog.csdn.net/q573625392/article/details/8931461
using Microsoft.Office.Interop.Excel;
....
protected FileContentResult ExportToExcel(string fileName, Dictionary<string, System.Data.DataTable> datasources)
{
/*
string tempFolders = Server.MapPath("\\Temp\\");
string templateName = "Template.xlsx";
if (!Directory.Exists(tempFolders))
{
Directory.CreateDirectory(tempFolders);
}
// Template file path.
string templateFileName = Path.Combine(Server.MapPath("\\Template\\"), templateName);
// Temp template file path.
string tempTemplateFile = Path.Combine(tempFolders, this.UserAlias + "-" + templateName);
// Copy file.
System.IO.File.Copy(templateFileName, tempTemplateFile, true);
*/
// Create a new application
Application excelApp = new Application();
excelApp.Visible = false;
// open a workbook
/*
Workbook newWorkbook = excelApp.Workbooks.Open(templateFileName, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
*/
Workbook newWorkbook = excelApp.Workbooks.Add();
int counter = 1;
foreach (var key in datasources.Keys)
{
System.Data.DataTable dataSource = datasources[key];
if (counter > 3)
{
newWorkbook.Worksheets.Add();
}
Worksheet excelSheet = (Worksheet)newWorkbook.Worksheets["Sheet" + counter.ToString()];
excelSheet.Name = key;
Range tempRange = null;
for (int i = 0; i < dataSource.Columns.Count; i++)
{
tempRange = excelSheet.get_Range(GetColumnNameByIndex(i) + "1", Type.Missing);
tempRange.Value2 = dataSource.Columns[i].ColumnName;
}
string text = string.Empty; ;
for (int i = 0; i < dataSource.Rows.Count; i++)
{
DataRow dr = dataSource.Rows[i];
for (int j = 0; j < dataSource.Columns.Count; j++)
{
text = dr[j].ToString();
tempRange = excelSheet.get_Range(GetColumnNameByIndex(j) + (i + 2).ToString());
tempRange.Value2 = text;
/*if (text.Length > 10 && text.Length < 50)
{
tempRange.Columns.ColumnWidth = text.Length * 1.5;
//tempRange.Columns.AutoFit();
}*/
}
}
counter++;
}
//range3.Borders.Color = Color.FromArgb(123, 231, 32).ToArgb();
//range3.Font.Color = Color.Red.ToArgb();
//range3.Font.Name = "Arial";
//range3.Font.Size = 9;
//range3.Orientation = 90; //vertical
//range3.Columns.HorizontalAlignment = Constants.xlCenter;
//range3.VerticalAlignment = Constants.xlCenter;
//range3.Interior.Color = Color.FromArgb(192, 192, 192).ToArgb();
//range3.Columns.AutoFit();//adjust the column width automatically
string tempFolderPath = Path.GetDirectoryName(fileName);
if (!Directory.Exists(tempFolderPath))
{
Directory.CreateDirectory(tempFolderPath);
}
if (System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
newWorkbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
newWorkbook.Close(false, Type.Missing, Type.Missing);
excelApp.Quit();
FileContentResult fcr = new FileContentResult(System.IO.File.ReadAllBytes(fileName), "application/ms-excel");
fcr.FileDownloadName = Path.GetFileName(fileName);
System.IO.File.Delete(fileName);
GC.Collect();
return fcr;
}
private static string[] alphabet = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
private static string GetColumnNameByIndex(int index)
{
if (index < alphabet.Length)
{
return alphabet[index];
}
return alphabet[index / alphabet.Length - 1] + alphabet[index % alphabet.Length];
}
}