在前一篇文章中,介绍了DataSet导出到Excel时客户端生成文件的几种思路,接着往下说,服务器端生成文件,用户直接下载,应该格式是可以保证的!
于是直接调用Excel的API生成。代码如下:
DataSetToLocalExcel
public static void DataSetToLocalExcel(DataSet dataSet, string outputPath, bool deleteOldFile)
{
if (deleteOldFile)
{
if (System.IO.File.Exists(outputPath)) { 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);
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;
}
//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;
// Release the Application object
excelApp.Quit();
excelApp = null;
// Collect the unreferenced objects
GC.Collect();
GC.WaitForPendingFinalizers();
}
public static void DataSetToLocalExcel(DataSet dataSet, string outputPath, bool deleteOldFile)
{
if (deleteOldFile)
{
if (System.IO.File.Exists(outputPath)) { 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);
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;
}
//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;
// Release the Application object
excelApp.Quit();
excelApp = null;
// Collect the unreferenced objects
GC.Collect();
GC.WaitForPendingFinalizers();
}
说明下,其中的 xlsApp.Application.DisplayAlerts = false; 的作用是不显示确认对话框
也可以逐Cell读取,那样可能会慢。本方法速度还过得去。
生成Winform代码测试没错,部署时,以为只要引用两个dll就可以了
Microsoft.Office.Interop.Excel.dll
Office.dll
那成想,问题接着来了,当在WebForm下调用时, 提示“检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046} 的组件时失败,原因是出现以下错误: 8000401a ”
晕! Google下,解决方案是在服务器上安装Office,并配置DCOM权限。步骤如下:
Code
配置 DCOM 中 EXCEL 应用程序:
要在交互式用户帐户下设置 Office 自动化服务器,请按照下列步骤操作:
1. 以管理员身份登录到计算机,并使用完整安装来安装(或重新安装)Office。为了实现系统的可靠性,建议您将 Office CD-ROM 中的内容复制到本地驱动器并从此位置安装 Office。
2. 启动要自动运行的 Office 应用程序。这会强制该应用程序进行自我注册。
3. 运行该应用程序后,请按 Alt+F11 以加载 Microsoft Visual Basic for Applications (VBA) 编辑器。这会强制 VBA 进行初始化。
4. 关闭应用程序,包括 VBA。
5. 单击开始,单击运行,然后键入 DCOMCNFG。选择要自动运行的应用程序。应用程序名称如下所示:
Microsoft Access 97 - Microsoft Access 数据库
Microsoft Access 2000/2002 - Microsoft Access 应用程序
Microsoft Excel 97/2000/2002 - Microsoft Excel 应用程序
Microsoft Word 97 - Microsoft Word Basic
Microsoft Word 2000/2002 - Microsoft Word 文档
单击属性打开此应用程序的属性对话框。
6. 单击安全选项卡。验证使用默认的访问权限和使用默认的启动权限已选中。
7. 单击标识选项卡,然后选择交互式用户。
8. 单击确定,关闭属性对话框并返回主应用程序列表对话框。
9. 在 DCOM 配置对话框中,单击默认安全性选项卡。
10. 单击访问权限的编辑默认值。验证访问权限中是否列出下列用户,如果没有列出,则添加这些用户:
SYSTEM
INTERACTIVE
Everyone
Administrators
IUSR_ <machinename> *
IWAM_ <machinename> *
* 这些帐户仅在计算机上安装了 Internet Information Server (IIS) 的情况下才存在。
11. 确保允许每个用户访问,然后单击确定。
12. 单击启动权限的编辑默认值。验证启动权限中是否列出下列用户,如果没有列出,则添加这些用户:
SYSTEM
INTERACTIVE
Everyone
Administrators
IUSR_ <machinename> *
IWAM_ <machinename> *
* 这些帐户仅在计算机上安装有 IIS 的情况下才存在。
13. 确保允许每个用户访问,然后单击确定。
14. 单击确定关闭 DCOMCNFG。
如果你之前起用了身份模拟 (在 web.config 中配置了 <identity impersonate= "true "/> ) ,需要删除之!
15.更新安装office,把.net可编程组件安装到本机(excel组件)
如果还是不行.干脃把交互式用户 换成"启动用户"
配置 DCOM 中 EXCEL 应用程序:
要在交互式用户帐户下设置 Office 自动化服务器,请按照下列步骤操作:
1. 以管理员身份登录到计算机,并使用完整安装来安装(或重新安装)Office。为了实现系统的可靠性,建议您将 Office CD-ROM 中的内容复制到本地驱动器并从此位置安装 Office。
2. 启动要自动运行的 Office 应用程序。这会强制该应用程序进行自我注册。
3. 运行该应用程序后,请按 Alt+F11 以加载 Microsoft Visual Basic for Applications (VBA) 编辑器。这会强制 VBA 进行初始化。
4. 关闭应用程序,包括 VBA。
5. 单击开始,单击运行,然后键入 DCOMCNFG。选择要自动运行的应用程序。应用程序名称如下所示:
Microsoft Access 97 - Microsoft Access 数据库
Microsoft Access 2000/2002 - Microsoft Access 应用程序
Microsoft Excel 97/2000/2002 - Microsoft Excel 应用程序
Microsoft Word 97 - Microsoft Word Basic
Microsoft Word 2000/2002 - Microsoft Word 文档
单击属性打开此应用程序的属性对话框。
6. 单击安全选项卡。验证使用默认的访问权限和使用默认的启动权限已选中。
7. 单击标识选项卡,然后选择交互式用户。
8. 单击确定,关闭属性对话框并返回主应用程序列表对话框。
9. 在 DCOM 配置对话框中,单击默认安全性选项卡。
10. 单击访问权限的编辑默认值。验证访问权限中是否列出下列用户,如果没有列出,则添加这些用户:
SYSTEM
INTERACTIVE
Everyone
Administrators
IUSR_ <machinename> *
IWAM_ <machinename> *
* 这些帐户仅在计算机上安装了 Internet Information Server (IIS) 的情况下才存在。
11. 确保允许每个用户访问,然后单击确定。
12. 单击启动权限的编辑默认值。验证启动权限中是否列出下列用户,如果没有列出,则添加这些用户:
SYSTEM
INTERACTIVE
Everyone
Administrators
IUSR_ <machinename> *
IWAM_ <machinename> *
* 这些帐户仅在计算机上安装有 IIS 的情况下才存在。
13. 确保允许每个用户访问,然后单击确定。
14. 单击确定关闭 DCOMCNFG。
如果你之前起用了身份模拟 (在 web.config 中配置了 <identity impersonate= "true "/> ) ,需要删除之!
15.更新安装office,把.net可编程组件安装到本机(excel组件)
如果还是不行.干脃把交互式用户 换成"启动用户"
折腾了一番,总算可以用了!·只是服务器上装Office总感觉不爽,于是再尝试下别的方法:
Reading and Writing Excel using OLEDB
主要的类文件如下:
ExcelReaderClass
/**//// <summary>
/// Summary description for ExcelReader.
/// </summary>
public class ExcelReader : IDisposable
{
Variables#region Variables
private int[] _PKCol;
private string _strExcelFilename;
private bool _blnMixedData = true;
private bool _blnHeaders = false;
private string _strSheetName;
private string _strSheetRange;
private bool _blnKeepConnectionOpen = false;
private OleDbConnection _oleConn;
private OleDbCommand _oleCmdSelect;
private OleDbCommand _oleCmdUpdate;
#endregion
properties#region properties
public int[] PKCols
{
get { return _PKCol; }
set { _PKCol = value; }
}
public string ColName(int intCol)
{
string sColName = "";
if (intCol < 26)
sColName = Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToChar((Convert.ToByte((char)'A') + intCol)));
else
{
int intFirst = ((int)intCol / 26);
int intSecond = ((int)intCol % 26);
sColName = Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToByte((char)'A') + intFirst);
sColName += Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToByte((char)'A') + intSecond);
}
return sColName;
}
public int ColNumber(string strCol)
{
strCol = strCol.ToUpper();
int intColNumber = 0;
if (strCol.Length > 1)
{
intColNumber = Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[1]) - 65);
intColNumber += Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[1]) - 64) * 26;
}
else
intColNumber = Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[0]) - 65);
return intColNumber;
}
public String[] GetExcelSheetNames()
{
System.Data.DataTable dt = null;
try
{
if (_oleConn == null) Open();
// Get the data table containing the schema
dt = _oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null) { return null; }
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows)
/**//// <summary>
/// Summary description for ExcelReader.
/// </summary>
public class ExcelReader : IDisposable
{
Variables#region Variables
private int[] _PKCol;
private string _strExcelFilename;
private bool _blnMixedData = true;
private bool _blnHeaders = false;
private string _strSheetName;
private string _strSheetRange;
private bool _blnKeepConnectionOpen = false;
private OleDbConnection _oleConn;
private OleDbCommand _oleCmdSelect;
private OleDbCommand _oleCmdUpdate;
#endregion
properties#region properties
public int[] PKCols
{
get { return _PKCol; }
set { _PKCol = value; }
}
public string ColName(int intCol)
{
string sColName = "";
if (intCol < 26)
sColName = Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToChar((Convert.ToByte((char)'A') + intCol)));
else
{
int intFirst = ((int)intCol / 26);
int intSecond = ((int)intCol % 26);
sColName = Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToByte((char)'A') + intFirst);
sColName += Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToByte((char)'A') + intSecond);
}
return sColName;
}
public int ColNumber(string strCol)
{
strCol = strCol.ToUpper();
int intColNumber = 0;
if (strCol.Length > 1)
{
intColNumber = Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[1]) - 65);
intColNumber += Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[1]) - 64) * 26;
}
else
intColNumber = Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[0]) - 65);
return intColNumber;
}
public String[] GetExcelSheetNames()
{
System.Data.DataTable dt = null;
try
{
if (_oleConn == null) Open();
// Get the data table containing the schema
dt = _oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null) { return null; }
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows)