项目开发过程中对excel导入导出操作的总结

1,CSV导出,存放在服务器端，然后给客户下载

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();  
   }

这是download页面