项目开发过程中对excel导入导出操作的总结 (转自同事的博客)
public void ExcelDownLoad(string strPageName,
string strColumnName,
string[] strFieldName,
DataTable datatable)
{
string excelName = "";
try
{
excelName = OA_Config.AppPath +@"\excelTemp\" + DateTime.Today.ToString("yyyyMMdd")+
new Random(DateTime.Now.Millisecond).Next(10000).ToString() +".xls";
FileStream fs=new FileStream(excelName,FileMode.Create,FileAccess.Write);
StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
sw.WriteLine(strColumnName);
StringBuilder strTextToAdd = new StringBuilder();
foreach(DataRow drs in datatable.Rows)
{
strTextToAdd = new StringBuilder();
for (int i = 0; i < strFieldName.Length; i++)
{
strTextToAdd.Append(drs[strFieldName[i]]);
if (i != strFieldName.Length - 1)
{
strTextToAdd.Append("\t");
}
}
sw.WriteLine(strTextToAdd);
}
sw.Close();
// // 指定返回的是一个不能被客户端读取的流，必须被下载
// response.ContentType = "application/Vnd.MS-Excel";
// response.AppendHeader("Content-Disposition", "attachment; filename=" + server.UrlEncode(excelName));
//
// // 把文件流发送到客户端
// response.WriteFile(excelName);
// response.End();
if( excelName.Equals("") )
{
Response.Redirect("../function/Error.aspx");
return;
}
excelName = excelName.Replace("\\","\\\\");
Page.RegisterStartupScript("dgscript","<script Language=\"Javascript\">iframe.document.location.href='../function/ExcelDownload.aspx?csv=" + excelName + "';</script>");
}
catch(Exception ex)
{
throw(ex) ;
}
finally{}
}
-----------------
调用过程
string[] strList = new string[]{"Hospitalcode","Hospitalname","Telephone","Naturegrade","Calltype","distributorname"};
ExcelDownLoad("",
"药店编号\t药店名称\t电话\t自然级别\t拜访类型\t总店",
strList,
table);
如果是csv文件，那么上面的/t直接换成,号就可以啦
2，利用xml
这种方法适合那种格式固定的excel，比如说行和列都是固定的模版excel.
System.Data.DataTable table = new System.Data.DataTable();
string xmlsqltemp = "";
try
{
table = 数据table
xmlsqltemp = OA_Config.AppPath +@"\excelTemp\" + DateTime.Today.ToString("yyyyMMdd")+
new Random(DateTime.Now.Millisecond).Next(10000).ToString() +".xml";
FileStream fs=new FileStream(xmlsqltemp,FileMode.Create,FileAccess.Write);
fs.Close();
System.IO.File.Copy( strPath, @xmlsqltemp, true );
System.IO.File.SetAttributes( @xmlsqltemp, System.IO.FileAttributes.Normal );
DataSet ds = new DataSet();
ds.ReadXml(xmlsqltemp);
ds.Tables[14].Rows[9]["Data_Text"] = table.Rows[0]["CallCount1"].ToString();
ds.Tables[14].Rows[10]["Data_Text"] = table.Rows[0]["CallCount2"].ToString();
ds.Tables[14].Rows[11]["Data_Text"] = table.Rows[0]["CallCount3"].ToString();
ds.Tables[14].Rows[2]["Data_Text"] = statisticentity.CalldateStart;
ds.Tables[14].Rows[3]["Data_Text"] = statisticentity.CalldateMiddle;
ds.Tables[14].Rows[4]["Data_Text"] = statisticentity.CalldateEnd;
ds.Tables[14].Rows[8]["Data_Text"] = statisticentity.EmployeeName;
ds.WriteXml(xmlsqltemp);
}
catch(Exception ex)
{
MessageLog.WriteLog(ex.ToString());
throw(ex);
}
return xmlsqltemp;
3.传统模式，需要服务器安装excel
Excel.Application excel = new Excel.Application(); //生成excel对象
Excel.Workbook myBook;
Excel.Worksheet mySheet;
Excel.Range rangeTemp;
object missing = System.Reflection.Missing.Value;//生成错误信息值,信息值为空
string excelsqltemp = OA_Config.AppPath +@"\excelTemp\" + DateTime.Today.ToString("yyyyMMdd")+
new Random(DateTime.Now.Millisecond).Next(10000).ToString() +".tmp";
FileStream fs=new FileStream(excelsqltemp,FileMode.Create,FileAccess.Write);
fs.Close();
System.IO.File.Copy( strPath, @excelsqltemp, true );
System.IO.File.SetAttributes( @excelsqltemp, System.IO.FileAttributes.Normal );
try
{
excel.Workbooks.Open(excelsqltemp,missing,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing,missing);
myBook = excel.Workbooks[1];
mySheet = (Excel.Worksheet)myBook.Worksheets[1];
//excel处理过程
excel.ActiveWorkbook.Save();
excel.Application.Quit();
excel.Quit();
GC.Collect();
}
catch(Exception ex)
{
MessageLog.WriteLog(ex.ToString());
throw(ex);
}
finally
{
GC.Collect();
}
return excelsqltemp;
4,页面table或datagird导出
引用别人的:
private void Page_Load(object sender, System.EventArgs e)
{
DataGrid1.DataSource=CreateDataSource();
DataGrid1.DataBind();
// 在此处放置用户代码以初始化页面
}
ICollection CreateDataSource()
{
DataTable dt = new DataTable();
DataRow dr;
dt.Columns.Add(new DataColumn("身份证号码", typeof(string)));
dt.Columns.Add(new DataColumn("图书单价",typeof(decimal)));
dt.Columns.Add(new DataColumn("购买数量",typeof(Int32)));
dt.Columns.Add(new DataColumn("总价格",typeof(decimal)));
for (int i = 0; i < 30; i++)
{
dr = dt.NewRow();
dr[0] = "123456789123456789";
dr[1] = 100 * i /3.0;
dr[2] = i + 5;
dr[3] = (decimal)dr[1] * (Int32)dr[2];
dt.Rows.Add(dr);
}
DataView dv = new DataView(dt);
return dv;
}
private void Button1_Click(object sender, System.EventArgs e)
{
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
private void Button1_Click(object sender, System.EventArgs e)
{
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
//Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
//Response.AppendHeader("Content-Disposition","attachment;filename=FileName.doc");
//Response.AppendHeader("Content-Disposition","attachment;filename=FileName.txt");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
//Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
//Response.ContentType="application/ms-word";
//Response.ContentType="application/ms-notepad";
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
5,客户端导出
<SCRIPT LANGUAGE="javascript">
function AutomateExcel()
{
window.oldOnError = window.onerror;
window.onerror = function (err)
{
if (err.indexOf('utomation') != -1)
{
alert('用户禁止了 WScript.Shell 的使用！');
return true;
}
else return false;
};
var xls = new ActiveXObject ( "Excel.Application" );
var x1FileName = "input.xls";
var x1Book = xls.Workbooks.Open(x1FileName);
var fname = xls.Application.GetSaveAsFilename("FOI Estimate.xls", "Excel Spreadsheets (*.xls), *.xls");
if (fname==""){
fname="C:\\FOI Estimate.xls";
}
x1Book.SaveAs(fname);
x1Book.Close;
xls.visible = false;
xls.Quit();
}
</SCRIPT>
但是这种方法必须设置ActiveX安全属性
对于系统的安全机制弹出个性化提示：
window.oldOnError = window.onerror;
window.onerror = function (err)
{
if (err.indexOf('utomation') != -1)
{
alert('用户禁止了 WScript.Shell 的使用！');
return true;
}
else return false;
};
var fso = new ActiveXObject("Scripting.FileSystemObject");
var wsc = new ActiveXObject("WScript.Shell");
window.onerror = window.oldOnError;
6,把excel当成数据源
strPath = OA_Config.AppPath +@"\excelTemp\" + DateTime.Today.ToString("yyyyMMdd")+
new Random(DateTime.Now.Millisecond).Next(10000).ToString() +".xls";
testcase_attach.PostedFile.SaveAs(strPath);
DataSet ds = new DataSet();
try
{
System.Data.OleDb.OleDbDataAdapter ad = new System.Data.OleDb.OleDbDataAdapter();
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + strPath + ";Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strConn);
string strSQL = "select * from [sheet1$]";
ad = new System.Data.OleDb.OleDbDataAdapter(strSQL, Conn);
ad.Fill(ds);
}
catch(Exception ex)
{
MessageLog.WriteLog(ex.ToString());
}
以上是开发过程中处理excel的各种方法，这里只是做个总结，没有具体的描述。
if(this.Request.Params["id"] != null)
{
string exceltemp=this.Request.Params["id"].ToString();
String ReportFileExc = exceltemp.Replace(".xml",".xls");
System.IO.File.Move(exceltemp,ReportFileExc);
System.IO.FileInfo fi = new System.IO.FileInfo(ReportFileExc);
Response.Clear();
Response.ClearHeaders();
Response.Buffer = false;
Response.ContentType = "application/octet-stream";
Response.AppendHeader("Content-Disposition","attachment;filename=" +HttpUtility.UrlEncode(fi.FullName,System.Text.Encoding.UTF8));
Response.AppendHeader("Content-Length",fi.Length.ToString());
Response.WriteFile(fi.FullName);
Response.Flush();
Response.End();
}
if(this.Request.Params["csv"] != null)
{
// HttpServerUtility server = new HttpServerUtility();
string exceltemp=this.Request.Params["csv"].ToString();
Response.Clear();
Response.ClearHeaders();
Response.Buffer = false;
Response.ContentType = "application/Vnd.MS-Excel";
Response.AppendHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(exceltemp));
Response.WriteFile(exceltemp);
Response.Flush();
Response.End();
}
同事博客：http://blog.csdn.net/happer6012
