ASP.NET中EXCEL的读写操作

最新推荐文章于 2022-06-13 16:49:03 发布

zm820630

最新推荐文章于 2022-06-13 16:49:03 发布

阅读量733

点赞数

文章标签： excel asp.net dataset string properties filter

本文链接：https://blog.csdn.net/zm820630/article/details/2384122

版权

本文介绍了一种使用C#进行Excel文件数据读取与写入的方法。包括如何通过OleDb连接Excel并从中读取数据到DataSet，如何获取Excel中的所有工作表名称，以及如何将DataTable中的数据写入Excel。此外，还提供了从DataGrid导出数据到Excel的示例。

摘要由CSDN通过智能技术生成

using System;

using System.Collections.Generic;

using System.Text;using System.Collections;using System.Data;

using System.Data.OleDb;

using System.IO;

namespace iPSAFee.SysFrameWork{

public class Excel {

//该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径，sheetname为表示那个Excel表；

static public DataSet ExcelDataSource(string filepath, string sheetname)

{

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=/"Excel 8.0;IEMX=1/"";

OleDbConnection conn = new OleDbConnection(strConn);

OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);

DataSet ds = new DataSet(); oada.Fill(ds); return ds; }

//获得Excel中的所有sheetname。

static public ArrayList ExcelSheetName(string filepath)

{

ArrayList al = new ArrayList();

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

conn.Close();

foreach (DataRow dr in sheetNames.Rows)

{ al.Add(dr[2]); } return al; }

//该方法实现将数据导入到Excel文件中，其中的DataTable dt就是你需要将数据写入到Excel中的数据；

static public void ExportExcel(DataTable dt, StreamWriter w)

{ try { for (int i = 0; i < dt.Columns.Count; i++) { w.Write(dt.Columns[i]); w.Write(' '); } w.Write(" "); object[] values = new object[dt.Columns.Count]; foreach (DataRow dr in dt.Rows) { values = dr.ItemArray; for (int i = 0; i < dt.Columns.Count; i++) { w.Write(values[i]); w.Write(' '); } w.Write(" "); } w.Flush(); w.Close(); } catch { w.Close(); } }

//filename为Excel的名字，ToExcelGrid就是数据源，在此为DataGrid数据源；

private void ExportExcelFromDataGrid(string filename, System.Web.UI.WebControls.DataGrid ToExcelGrid)

{ //Response.Clear(); //Response.Buffer = true; //Response.Charset = "utf-8"; //Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(filename)); //Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");//设置输出流为简体中文 //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); //ToExcelGrid.RenderControl(oHtmlTextWriter); //Response.Write(oStringWriter.ToString()); //Response.End(); }

public static DataSet DS_ReadExcel(System.Web.UI.HtmlControls.HtmlInputFile ctl, string mPath, string sheetName, string filter, ref string err, ref string fullPath) { string filePath = "", fileExtName = "", mFileName; if (ctl.PostedFile.FileName != "") { try { DataSet ds = new DataSet(); filePath = ctl.PostedFile.FileName; //文件名 fileExtName = filePath.Substring(filePath.LastIndexOf(".") + 1); //文件后缀名 mFileName = filePath.Substring(filePath.LastIndexOf("//") + 1); //保存名称 if (fileExtName.ToLower() != "xls" && fileExtName.ToUpper() != "XlS") { err = "3805"; return null; } string StrDateTime = System.DateTime.Now.ToString("yyyyMMddhhmmss"); fullPath = mPath + StrDateTime + mFileName; //上传文件完整路径 ctl.PostedFile.SaveAs(fullPath); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fullPath + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = "select * from [" + sheetName + "$]"; if (filter != "") { strExcel += " where "; strExcel += filter; } myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, "table1"); conn.Close(); return ds; } catch { err = "3807"; return null; } } else { return null; } }

public static DataSet DS_ReadExcel(string sheetName, string filter, ref string err, string fullPath) { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fullPath + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); try { DataSet ds = new DataSet(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = "select * from [" + sheetName + "$]"; if (filter != "") { strExcel += " where "; strExcel += filter; } myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, "table1"); conn.Close(); return ds; } catch { conn.Close(); err = "3807"; return null; } }

//上传Excel文件

public static string UploadFile(System.Web.UI.HtmlControls.HtmlInputFile ctl, string mPath, long UserID, ref string err) { string filePath = "", fileExtName = "", mFileName; try { string fullPath = ""; filePath = ctl.PostedFile.FileName; //文件名 fileExtName = filePath.Substring(filePath.LastIndexOf(".") + 1); //文件后缀名 mFileName = filePath.Substring(filePath.LastIndexOf("//") + 1); //保存名称 if (fileExtName.ToLower() != "xls" && fileExtName.ToUpper() != "XlS") { err = "3805"; return ""; } string StrDateTime = System.DateTime.Now.ToString("yyyyMMddhhmmss"); fullPath = mPath + StrDateTime + UserID.ToString() + mFileName; //上传文件完整路径 ctl.PostedFile.SaveAs(fullPath); return fullPath; } catch { err = "3807"; return ""; } } }}