asp.net中操作Excel
1. 采用采用Aspose.Cells
string path = page.MapPath(".");
string license = page.Server.MapPath("~/Aspose.Cells.lic");
License lic = new License();
lic.SetLicense(license);
templatePath = path + templatePath;
Workbook workbook = new Workbook();
workbook.Open(templatePath);
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
cells.ImportDataTable(dt, true, 0, 0);
for (int i = 0, j = -1; i < cells.Columns.Count; i++)
{
//删除无用列
j = columnNameList.IndexOf(cells[0, i].Value.ToString());
if (j > -1)
{
//找到了列名定义
cells[0, i].PutValue(columnTitleList[j].ToString());
}
else
{
//需要删除的列
cells.DeleteColumn(i);
i = i - 1;
}
}
for (int j = 0; j < cells.Columns.Count; j++)
{
//设置样式
cells[0, (byte)j].Style.HorizontalAlignment = TextAlignmentType.Center;
cells[0, (byte)j].Style.Font.Color = Color.Blue;
cells[0, (byte)j].Style.Font.IsBold = true;
cells[0, (byte)j].Style.Font.Size = 11;
if (widthList.Count > j && widthList[j] != null)
cells.SetColumnWidth(j, double.Parse(widthList[j]));//宽度
else
cells.SetColumnWidth(j, 11);//默认宽度
}
sheet.Name = dt.TableName.ToString();
while (workbook.Worksheets.Count > 1)
{
workbook.Worksheets.RemoveAt(workbook.Worksheets.Count - 1);
}
string name = HttpUtility.UrlEncode(fileName) + ".xls";
workbook.Save("" + name + "", SaveType.OpenInExcel, FileFormatType.Default, page.Response);
2. 采用Owc11的版本
//说明：Owc11的列和行都是从1开始计算，不是0
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
//处理Excel列名
for (int i = 0; i < columnTitleList.Count; i++)
{
xlsheet.ActiveSheet.Cells[1, i + 1] = columnTitleList[i];
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < columnNameList.Count; j++)
{
xlsheet.ActiveSheet.Cells[i + 2, j + 1] = dt.Rows[i][columnNameList[j]].ToString();
}
}
//开始设置格式
int iRowsCount = dt.Rows.Count + 1;
int iColumnsCount = columnNameList.Count;
Owc11.Range ExcelTitle = xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, iColumnsCount]);//Excel列名部分
Owc11.Range ExcelContent = xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[iRowsCount, iColumnsCount]);//Excel数据部分
ExcelTitle.Font.set_Size(10);
//ExcelTitle.Font.set_Color(Blue);
//ExcelTitle.Interior.set_Color(Color.Yellow);
ExcelTitle.set_HorizontalAlignment(Owc11.XlHAlign.xlHAlignCenter);
ExcelContent.Font.set_Size(9);
ExcelContent.set_HorizontalAlignment(Owc11.XlHAlign.xlHAlignLeft);//默认居左
//循环所有列
for (int i = 0; i < iColumnsCount; i++)
{
//处理宽度
if (widthList.Count > i && widthList[i] != null)
xlsheet.get_Range(xlsheet.Cells[1, i + 1], xlsheet.Cells[1, i + 1]).set_ColumnWidth(double.Parse(widthList[i]));//宽度
else
xlsheet.get_Range(xlsheet.Cells[1, i + 1], xlsheet.Cells[1, i + 1]).set_ColumnWidth(50);//默认宽度
switch (columnValueTypeList[i].ToString())
{
case "text":
xlsheet.get_Range(xlsheet.Cells[2, i + 1], xlsheet.Cells[2, i + 1]).set_NumberFormat("@");//文本
break;
case "date":
xlsheet.get_Range(xlsheet.Cells[2, i + 1], xlsheet.Cells[2, i + 1]).set_NumberFormat("yyyy-mm-dd");//日期
xlsheet.get_Range(xlsheet.Cells[2, i + 1], xlsheet.Cells[2, i + 1]).set_HorizontalAlignment(Owc11.XlHAlign.xlHAlignCenter);//居中
break;
case "number":
xlsheet.get_Range(xlsheet.Cells[2, i + 1], xlsheet.Cells[2, i + 1]).set_NumberFormat("#,##0.00");//数字
xlsheet.get_Range(xlsheet.Cells[2, i + 1], xlsheet.Cells[2, i + 1]).set_HorizontalAlignment(Owc11.XlHAlign.xlHAlignRight);//居右
break;
case "money":
xlsheet.get_Range(xlsheet.Cells[2, i + 1], xlsheet.Cells[2, i + 1]).set_NumberFormat("￥#,##0.00");//货币
xlsheet.get_Range(xlsheet.Cells[2, i + 1], xlsheet.Cells[2, i + 1]).set_HorizontalAlignment(Owc11.XlHAlign.xlHAlignRight);//居右
break;
case "percent":
xlsheet.get_Range(xlsheet.Cells[2, i + 1], xlsheet.Cells[2, i + 1]).set_NumberFormat("#0.00%");//百分比
xlsheet.get_Range(xlsheet.Cells[2, i + 1], xlsheet.Cells[2, i + 1]).set_HorizontalAlignment(Owc11.XlHAlign.xlHAlignRight);//居右
break;
default:
xlsheet.get_Range(xlsheet.Cells[2, i + 1], xlsheet.Cells[2, i + 1]).set_NumberFormat("@");//默认为文本
break;
}
}
//冻结窗口 效果没出来
xlsheet.get_Range(xlsheet.Cells[2, 2], xlsheet.Cells[2, 2]).Select();
xlsheet.ActiveWindow.FreezePanes = false;
xlsheet.ActiveWindow.FreezePanes = true;
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, 1]).Select();
//设置边框
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[iRowsCount, iColumnsCount]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);
//xlsheet.ActiveSheet.Visible = true;
xlsheet.Export("C:\\test\\" + fileName, Owc11.SheetExportActionEnum.ssExportActionNone, Owc11.SheetExportFormat.ssExportHTML);
return xlsheet.HTMLData.ToString();
3. 采用Excel.dll
//excel的定义
Excel.Application excel = new Application();
Excel._Workbook xBk;
Excel._Worksheet xSt;
excel = new ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
//处理Excel列名
for (int i = 0; i < columnTitleList.Count; i++)
{
excel.Cells[1, i + 1] = columnTitleList[i];
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < columnNameList.Count; j++)
{
excel.Cells[i + 2, j + 1] = dt.Rows[i][columnNameList[j]].ToString();
}
}
//开始设置格式
int iRowsCount = dt.Rows.Count + 1;
int iColumnsCount = columnNameList.Count;
Excel.Range ExcelTitle = xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, iColumnsCount]);//Excel列名部分
Excel.Range ExcelContent = xSt.get_Range(excel.Cells[2, 1], excel.Cells[iRowsCount, iColumnsCount]);//Excel数据部分
ExcelTitle.Font.Size = 10;
ExcelTitle.Font.Bold = true;
ExcelTitle.Font.ColorIndex = 32;
ExcelTitle.Interior.ColorIndex = 19;
ExcelTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter;
ExcelContent.Font.Size = 9;
ExcelContent.HorizontalAlignment = XlHAlign.xlHAlignLeft;//默认居左
//循环所有列
for (int i = 0; i < iColumnsCount; i++)
{
//处理宽度
if (widthList.Count > i && widthList[i] != null)
xSt.get_Range(excel.Cells[1, i + 1], excel.Cells[1, i + 1]).ColumnWidth = double.Parse(widthList[i]);//宽度
else
xSt.get_Range(excel.Cells[1, i + 1], excel.Cells[1, i + 1]).ColumnWidth = 50;//默认宽度
switch (columnValueTypeList[i].ToString())
{
case "text":
xSt.get_Range(excel.Cells[2, i + 1], excel.Cells[iRowsCount, i + 1]).NumberFormat = "@";//文本
break;
case "date":
xSt.get_Range(excel.Cells[2, i + 1], excel.Cells[iRowsCount, i + 1]).NumberFormat = "yyyy-mm-dd";//日期
xSt.get_Range(excel.Cells[2, i + 1], excel.Cells[iRowsCount, i + 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;//居中
break;
case "number":
xSt.get_Range(excel.Cells[2, i + 1], excel.Cells[iRowsCount, i + 1]).NumberFormat = "#,##0.00";//数字
xSt.get_Range(excel.Cells[2, i + 1], excel.Cells[iRowsCount, i + 1]).HorizontalAlignment = XlHAlign.xlHAlignRight;//居右
break;
case "money":
xSt.get_Range(excel.Cells[2, i + 1], excel.Cells[iRowsCount, i + 1]).NumberFormat = "￥#,##0.00";//货币
xSt.get_Range(excel.Cells[2, i + 1], excel.Cells[iRowsCount, i + 1]).HorizontalAlignment = XlHAlign.xlHAlignRight;//居右
break;
case "percent":
xSt.get_Range(excel.Cells[2, i + 1], excel.Cells[iRowsCount, i + 1]).NumberFormat = "#0.00%";//百分比
xSt.get_Range(excel.Cells[2, i + 1], excel.Cells[iRowsCount, i + 1]).HorizontalAlignment = XlHAlign.xlHAlignRight;//居右
break;
default:
xSt.get_Range(excel.Cells[2, i + 1], excel.Cells[iRowsCount, i + 1]).NumberFormat = "@";//默认为文本
break;
}
}
//冻结窗口
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Select();
excel.ActiveWindow.FreezePanes = false;
excel.ActiveWindow.FreezePanes = true;
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Select();
//设置边框
xSt.get_Range(excel.Cells[1, 1], excel.Cells[iRowsCount, iColumnsCount]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
//开始导出
excel.Visible = true;
xBk.SaveCopyAs("C:\\test\\" + fileName);
在作了一些测试和比较后,最终我选择了第三种方式(采用Excel.dll )
对于第一种方式,采用Aspose.Cells,这个控件需要license,而且又是第三方控件,会对项目的成本以及扩充性带来一定的影响.
对于第二种方式,采用OWC11,由于是Office安装时自带的,而且是专门为web应用而设计,的确是很适合在这种场合下应用,但OWC11的内部操作都是采用xml的格式,而且只能导出为xml和html格式,但不能导出为标准的xls格式,如果只是在web上显示,owc无疑是最好的选择,但本项目中还需要考虑到用本地桌面Excel应用程序来打开,所以对于导出为xls格式要求尤为重要,因为随着wps的普及,很多客户端都是采用wps excel作为Excel的应用程序,而wps不支持xml和html格式,所以考虑到这个情况,放弃了OWC11这个选择
对于第三种方式,采用Excel.dll,这种方式最大的优点就是可以导出为标准的xls格式,而且支持很多诸如字体,公式,冻结窗口等设置,虽然效率是比owc要稍微差点,但也是一个不错的选择.
