在做项目的过程中,基本上都会和Excel打交道。这不仅仅是用户习惯,还因为Excel强大的二次数据处理能力。
这些年来,导出Excel的方式也用过很多种,在这里盘点一下,做个记录。
1.利用HttpResponse直接浏览器输出。
这个从代码上来说,比较简单,但生成的不是原生Excel编码,懒人专用,下面给出可直接套用的代码。
ExportToExcel(this.Page, "举例", dt);
public void ExportToExcel(Page page, string fileName, DataTable tb)
{
string html = GetHtml(tb);
page.Response.Clear();
page.Response.Buffer = true;
page.Response.Charset = "UTF-8";
page.Response.ContentEncoding = System.Text.Encoding.UTF8;
page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.DateTime.Now.ToString("yyMMddhhmm") + ".xls");
page.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
page.EnableViewState = false;
page.Response.Write(html);
page.Response.End();
}
public string GetHtml(DataTable dt)
{
StringBuilder data = new StringBuilder();
data.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
string html = "<tr style=\"font-weight: bold; white-space: nowrap;\">"
+ "<th scope=\"col\">姓名</th><th scope=\"col\">金额(元) </th><th scope=\"col\">时间</th></tr>";
string[] LCoumn = { "NAME", "FEE", "ADDTIME" };
data.AppendLine(html);
string rowSpan = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
data.AppendLine("<tr>");
for (int k = 0; k < LCoumn.Length; k++)
{
//这里自行处理
data.AppendLine("<td style=\"vnd.ms-excel.numberformat:@\">" + dt.Rows[i][LCoumn[k]].ToString() + "</td>");
}
data.AppendLine("</tr>");
}
data.AppendLine("</table>");
return data.ToString();
}
2.JS使用Excel的ActiveXObject 导出,这个是利用剪切板导出页面内容,而且受限ActiveX无法支持chrome、edge等浏览器。
function CellToExcel(tableID) {
try {
var curTbl = document.getElementById(tableID);
// 加载ActiveX控件,获取Excel句柄
var exApp = new ActiveXObject("Excel.Application");
// 创建一个Excel文件
var oWB = exApp.WorkBooks.Add();
// 获取sheet1句柄CA
//var exSheet = exApp.ActiveWorkBook.WorkSheets(1);
var exSheet = oWB.ActiveSheet;
exSheet.Cells.NumberFormatLocal = "@";
var filename = "日志";
exSheet.name = filename;
// copy指定的表格
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
sel.select();
sel.execCommand("Copy");
// 粘贴到sheet中
exSheet.Paste();
//在保存前设置DisplayAlerts属性,即可屏蔽该对话框
exApp.DisplayAlerts = false;
var date = new Date(); //日期对象
var now = "";
now = date.getFullYear();
now = now + "" + (date.getMonth() + 1);
now = now + "" + date.getDate();
now = now + "" + date.getHours();
now = now + "" + date.getMinutes();
now = now + "" + date.getSeconds();
exSheet.SaveAs("d:\\" + filename + "(" + now + ").xls");
// 退出Excel实例
exApp.Quit();
exApp = null;
// 调用Cleanup()进行垃圾回收
idTmr = window.setInterval("Cleanup();", 10);
alert("导出完成,已保存到 D://" + filename + "(" + now + ").xls!");
//聚焦后取消表格选中
document.getElementById("txt").focus();
}
catch (ex) {
exApp.Quit();
exApp = null;
//用户点击保存对话框中的取消按钮时会发生异常
}
}
// 函数功能:杀掉Excel进程
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
3.使用OleDb导出,然后输出到浏览器保存。
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tempfilePath + ";Extended Properties=Excel 8.0;";
这个连接串用于2003版本,而且在64位服务器中会报错,64位服务器和07以上版本应使用下面这个
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + source + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
连接串释义:
Provider:驱动程序名称
Data Source:Excel 文件路径
Extended Properties:Excel 8.0 针对 Excel 2000 及以上版本;Excel 12.0 针对 Excel 2007 及以上版本。
HDR:Yes 表示第一行包含列名,在计算行数时就不包含第一行。NO 则完全相反。
IMEX:0 写入模式;1 读取模式;2 读写模式。如果报错为“不能修改表 sheet1 的设计。它在只读数据库中”,那就去掉这个,问题解决。
try
{
string tempfilePath = Server.MapPath(Guid.NewGuid() + ".Xls");
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tempfilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
string SqlStrCreate = "insert into...";//写入导出的数据,可在这里做个循环处理
objConn.Open();
objCmd.CommandText = SqlStrCreate;
objCmd.ExecuteNonQuery();
objCmd.Dispose();
objConn.Dispose();
HttpResponse resp = Page.Response;
resp.Clear();
resp.WriteFile(tempfilePath);
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.ContentType = "application/ms-excel";
resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("测试.xls", Encoding.UTF8));
resp.Flush();
resp.End();
}
catch (Exception ex)
{
}
finally
{
}
4.C#使用Microsoft.Office.Interop.Excel组件导出。缺点:慢,且虽然做了释放处理仍会进程驻留。
public static void DataViewAppendToExcel2007(System.Web.UI.Page page, System.Data.DataTable dt, string filePath, string sheetName, bool isNewSheet)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbook wBook = app.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet wSheet = wBook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
try
{
if (dt.Rows.Count > 0)
{
int row = 0;
row = dt.Rows.Count;
int col = dt.Columns.Count;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
string str = dt.Rows[i][j].ToString();
wSheet.Cells[i + 2, j + 1] = str;
}
}
}
int size = dt.Columns.Count;
for (int i = 0; i < size; i++)
{
wSheet.Cells[1, 1 + i] = dt.Columns[i].ColumnName;
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wBook.Saved = true;
Save2007(wBook, filePath);
app.Workbooks.Close();
app.Quit();
ReleaseObj(wSheet);
ReleaseObj(wBook);
ReleaseObj(app);
wSheet = null;
wBook = null;
app = null;
GcCollect();
string saveFileName;
saveFileName = "Excel2007.xlsx";
page.Response.Clear();
page.Response.Charset = "utf-8";
page.Response.Buffer = true;
page.Response.ContentEncoding = System.Text.Encoding.UTF8;
page.Response.AppendHeader("Content-Disposition", "attachment;FileName=" + saveFileName);
page.Response.WriteFile(filePath);
page.Response.Flush();
page.Response.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
ReleaseObj(wSheet);
ReleaseObj(wBook);
ReleaseObj(app);
wSheet = null;
wBook = null;
app = null;
GcCollect();
}
}
5.Aspose.Cells控件。曾经有一两年,特别喜欢用它,因为用它无需安装Microsoft Excel,而且功能也挺强大。但是,付费价格实在美丽,破解又心有疑虑。
/// <summary>
/// 导出数据
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="StrNFileName">文件</param>
public static void OutFileToDisk(DataTable dt, string StrNFileName)
{
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells;//单元格
//为标题设置样式
Style styleTitle = workbook.Styles[workbook.Styles.Add()];//新增样式
styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中
styleTitle.Font.Name = "宋体";//文字字体
styleTitle.Font.Size = 18;//文字大小
styleTitle.Font.IsBold = true;//粗体
//样式2
Style Headstyle = workbook.Styles[workbook.Styles.Add()];//新增样式
Headstyle.HorizontalAlignment = TextAlignmentType.Center;//文字居中
Headstyle.Font.Name = "宋体";//文字字体
Headstyle.Font.Size = 14;//文字大小
Headstyle.Font.IsBold = true;//粗体
Headstyle.IsTextWrapped = true;//单元格内容自动换行
Headstyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
Headstyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
Headstyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
Headstyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//样式3
Style Rowstyle = workbook.Styles[workbook.Styles.Add()];//新增样式
Rowstyle.HorizontalAlignment = TextAlignmentType.Center;//文字居中
Rowstyle.Font.Name = "宋体";//文字字体
Rowstyle.Font.Size = 12;//文字大小
Rowstyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
Rowstyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
Rowstyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
Rowstyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
int Colnum = dt.Columns.Count;//表格列数
int Rownum = dt.Rows.Count;//表格行数
//生成标题行
cells.Merge(0, 0, 1, Colnum);//合并单元格
cells[0, 0].PutValue("测试");//填写内容
cells[0, 0].SetStyle(styleTitle);
cells.SetRowHeight(0, 38);
//生成表头
for (int i = 0; i < Colnum; i++)
{
cells[1, i].PutValue(dt.Columns[i].ColumnName);
cells[1, i].SetStyle(Headstyle);
cells.SetRowHeight(1, 25);
}
//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[2 + i, k].PutValue(dt.Rows[i][k].ToString());
cells[2 + i, k].SetStyle(Rowstyle);
}
cells.SetRowHeight(2 + i, 24);
}
workbook.Save(StrNFileName);
}
6.现在除了懒人做法,主要就是用NPIO了,开源免费,写法和Aspose.Cells差不多。
记得好几年前刚开始用的时候,比较生涩,还自己去代码编写复杂表头,写得火了,就想着能不能用模板来处理。
直接Excel定义好模板,然后NPIO写入数据。哈哈,还真可以的,当时这个没百度。
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
string path = Application.StartupPath;
string sourceFile = path + @"\moban.xlsx";
string StrNFileName = path + @"\测试" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
FileInfo f = new FileInfo(sourceFile);
File.Copy(f.FullName, StrNFileName, true);
TableToExcelForXLSX(dt, StrNFileName);
/// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
private void TableToExcelForXLSX(DataTable dt, string file)
{
IWorkbook wk = null;
string extension = System.IO.Path.GetExtension(file);
try
{
FileStream fs = File.OpenRead(file);
//把xlsx文件中的数据写入wk中
wk = new XSSFWorkbook(fs);
fs.Close();
}
catch
{
}
//创建样式
ICellStyle cellstyle = wk.CreateCellStyle();
cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellstyle.WrapText = true;
IFont cellfont = wk.CreateFont();
cellstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Hair;
cellstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium;
cellstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Dotted;
cellfont.Boldweight = short.MaxValue;
cellstyle.SetFont(cellfont);
//读取当前表数据
ISheet sheet = wk.GetSheetAt(0);
//创建一行,此行为第一行
if (dt != null && dt.Rows.Count > 0)
{
//数据
for (Int32 i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (Int32 j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = cellstyle;
string colValue = dt.Rows[i][j].ToString();
cell.SetCellValue(colValue);
}
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
wk.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
以上代码都是自用的,基本上没遮掩,即拿即用。好用别忘点赞。
同步发布于本人个人网站:洋气的鱼 (microhis.top)