Excel转换成DataTable DataTable转换成Excel

最新推荐文章于 2024-02-29 13:55:53 发布

Alice_1011

最新推荐文章于 2024-02-29 13:55:53 发布

阅读量3.9k

点赞数

本文链接：https://blog.csdn.net/qq_27421041/article/details/70209169

版权

<div class="upload">

   <asp:FileUpload ID="uploadFile" style="outline:none;" runat="server" BorderStyle="None" >
</div>

using Aspose.Cells;

if(uploadFile.HasFile)
{
   string fileContentType=uploadFile.PostedFile.ContentType;
   string name=Path.GetFileName(uploadFile.PostedFile.FileName);
   string filePath=Server.MapPath("/UploadFiles/Enterprise/djs/");
   string webFilePath=Server.MapPath("/UploadFiles/Enterprise/djs/"+name);
  
   if(Directory.Exists(filePath)==false) //判断目录是否存在
   {
      Directory.CreateDirectory(filePath);
   }

   uploadFile.SaveAs(webFilePath); //上传
   
   DataTable dt=ExcelToDt(webFilePath); //Excel转化成DataTable

}

public DataTable ExcelToDt(string path)
{
   Aspose.Cells.Workbook workbook=new Aspose.Cells.Workbook();
   workbook.Open(path);
   Aspose.Cells.Worksheet worksheet=workbook.Worksheets[0];
   DataTable dt=worksheet.Cells.ExportDataTable(1,0,worksheet.Cells.MaxRow+1,worksheet.Cells.MaxColumn+1);
   return dt;
}

注：需要引用Aspose.Cells的动态库_ http://download.csdn.net/download/qq_27421041/9816409

DataTable转换成Excel

protected void GetExcel()
{
   DataTable dtNew=new DataTable();
   DataColumn dc=null;
   dc=dtNew.Columns.Add("id");
   dc=dtNew.Columns.Add("name");
   dc=dtNew.Columns.Add("type");
   
   DataTable dt1=GetUserInfo1();
   DataTable dt2=GetUserInfo2();

   for(int i=0;dt1.Rows.Count;++i)
   {
      DataRow newRow=dtNew.NewRow();
      if(dt1.Rows[i][1].ToString()!="")
         newRow["id"]=dt1.Rows[i][1].ToString();
      else
         newRow["id"]="";
      if(dt1.Rows[i][2].ToString()!="")
         newRow["name"]=dt1.Rows[i][2].ToString();
      else
         newRow["name"]="";
      if(dt2.Rows[i][1].ToString()!="")
         newRow["type"]=dt2.Rows[i][1].ToString(); 
      else
         newRow["type"]="";
      
      dtNew.Rows.Add(newRow);
    }
    DataTableToExcel(dtNew,"user_"+DateTime.Now.ToShortDateString()+".xls");

}

protected DataTable GetUserInfo1()
{
   string sql="";
   return DbHelper.ExecuteTable(CommandType.Text,sql,null);
}
protected DataTable GetUserInfo2()
{
   string sql="";
   return DbHelper.ExecuteTable(CommandType.Text,sql,null);
}

public static void DataTableToExcel(DataTable dt,string FileName)
{
   HttpResponse resp;
   resp=System.Web.HttpContext.Current.Response;
   resp.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
   resp.AppendHeader("Content-Disposition","attachment;filename="+FileName);
   string colHeaders="",Is_item="";
   
   //定义表对象与行对象，同时用法DataSet对其值进行初始化
   DataRow[] myRow=dt.Select(); //可以类似dt.Select("id>10")之形式达到数据筛选目的
   int i=0;
   int cl=dt.Columns.Count;

   //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
   for(int i=0;i<cl;i++)
   {
      if(i==(cl-1)) //最后一列，加\n
         colHeaders+=dt.Rows[i].Caption.ToString()+"\n";
      else
         colHeaders+=dt.Rows[i].Caption.ToString()+"\t";
   }   
   resp.Write(colHeaders);
   //向HTTP输出流中写入取得的数据信息

   //每一行 处理数据
   foreach(DataRow row in myRow)
   {
      //当前行数据写入HTTP输出流，并且置空Is_item以便下行数据
      for(i=0;i<c1;i++)
      {
         if(i==(cl-1)) //最后一列，加\n
            Is_item+=row[i].ToString()+"\n";
         else
            Is_item+=row[i].ToString()+"\t";
       }
       resp.Write(Is_item);
       Is_item="";
   }
   resp.End();

}