求救啊！亲们 Workbook 问题
今天做一个导出功能，excel.Save这个方法总会弹出“打开”“保存”“取消”三个按钮；可是我折腾了半天都没有折腾出来，就是想点击导出按钮的时候能不能知道保存到我想要的路径里面，这折腾的我脸都绿了；也没有发现有 SaveAs方法。
///// <summary>
///// 导出Excel文件
///// </summary>
///// <param name="res"></param>
///// <param name="Datas">一个列表表示一个Sheet</param>
public static void ExportExcel(HttpResponse res, List<DataTable> Datas)
{
Workbook excel = new Workbook();
excel.Worksheets.Clear();
for (int i = 0; i < Datas.Count; i++)
{
excel.Worksheets.Add(Datas[i].TableName);
excel.Worksheets[Datas[i].TableName].Cells.ImportDataTable(Datas[i], true, 0, 0, true);
}
excel.Save(DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", SaveType.OpenInExcel, FileFormatType.Default, res);
}
[解决办法]
//方法
public class DataToExcel
{
public static void Export(string[] fileName, string[] title, string tableName, string sheetName, string strWhere, string sort)
{
string sql = "select ";
if (fileName == null
[解决办法]
fileName.Length < 1)
{
sql += "* ";
}
else
{
foreach (string key in fileName)
{
sql += key + ",";
}
sql = System.Text.RegularExpressions.Regex.Replace(sql, ",$", " ");
}
sql += "from " + tableName;
if (!string.IsNullOrEmpty(strWhere))
{
sql += " where " + strWhere;
}
if (!string.IsNullOrEmpty(sort))
{
sql += " order by " + sort;
}
//获取需要导出的数据
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.Row row = sheet.CreateRow(0);
//设置列名称和数据
if (title == null
[解决办法]
title.Length < 1)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
else
{
for (int i = 0; i < title.Length; i++)
{
row.CreateCell(i).SetCellValue(title[i].ToString());
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < fileName.Length; j++)
row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Export_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"));
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
}
//导出代码
protected void But_Excel_Click(object sender, EventArgs e)
{
string[] fileName = { "ID","HtmlName","PageTitle","PageKey","PageDes","Title","Form","Image","ITitle","IAlt","Intor","Content","IsPass","IsTop","Sort","Click","AddTime" };
string[] title = { "编号","Htm名","页面标题","页面关键字","页面描述","新闻标题","新闻来源","图片","图片Title","图片Alt","新闻简介","新闻详细介绍","审核","首页显示","排序","点击数","添加时间" };
DataToExcel.Export(fileName, title, "TB_News", "安信动态列表_" + DateTime.Now.ToString("yyy-MM-dd"), getStrWhere(), getSort());
}
object missing = Type.Missing;
Workbook excel = new Workbook();
//
excel.SaveAs("路径", missing, missing, missing, missing, missing
, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
private void Export()
{
if (zhifaGUID != "")
{
string tempPath = OperateReport.ZhiFaExport(zhifaGUID);
string ReportFilePath = "http://" + Request.Url.Host + Common.UpLoadDir + tempPath;
Response.Redirect(ReportFilePath, false);
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('抱歉，导出错误！');window.self.close();</script>");
return;
}
}
