刚刚初入职场，在休闲的时间写下了项目中用到的对Excel文档操作的方法以及总结，多的不说，直接上代码
public static void CreateExcel(DataSet ds, string FileName)
{
//resp = Page.Response;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
HttpContext.Current.Response.ContentType = "application/ms-excel";
string colHeaders = "", ls_item = "";
int i = 0;
//定¨义?表括?对?象ó与?行D对?像?，?同?时骸?用?DataSet对?其?值μ进?行D初?始?化ˉ
System.Data.DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select("");
//取?得?数簓据Y表括?各÷列标括?题琣，?各÷标括?题琣之?间?以?\t分?割?，?最?后ó一?个?列标括?题琣后ó加ó回?车μ符?
for (i = 0; i < dt.Columns.Count - 1; i++)
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
//向òHTTP输?出?流ⅰ?中D写′入?取?得?的?数簓据Y信?息￠
HttpContext.Current.Response.Write(colHeaders);
//逐e行D处鋦理え?数簓据Y
foreach (DataRow row in myRow)
{
//在ú当獭?前°行D中D，?逐e列获?得?数簓据Y，?数簓据Y之?间?以?\t分?割?，?结á束?时骸?加ó回?车μ符?\n
for (i = 0; i < dt.Columns.Count - 1; i++)
ls_item += row[i].ToString() + "\t";
ls_item += row[i].ToString() + "\n";
//当獭?前行D数簓据Y写′入?HTTP输?出?流ⅰ?，?并￠且ò置?空?ls_item以?便?下?行D数簓据Y
HttpContext.Current.Response.Write(ls_item);
ls_item = "";
}
//写′缓o冲?区?中D的?数簓据Y到?HTTP头?文?件t中D
HttpContext.Current.Response.End();
}
方法2:NPIO导出Excel数据。这种方式方便快捷，要引入第三方的NPIO.dll的文件，下载地址http://npoi.codeplex.com/releases/view/38113
protected void getExcel(DataTable dt, string FileName)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
headerrow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
//写′入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls"));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
直接在点击事件调用即可
二，导入Excel数据
导入Excel需要引用微软官方的OleDb驱动
protected void btnUpload_Click(object sender, EventArgs e)
{
if (txtFilePath.HasFile)
{
OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
string query = null;
string connString = "";
//string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower();
if (strFileType == ".xls" || strFileType == ".xlsx")
{
txtFilePath.SaveAs(Server.MapPath("~/upload/" + strFileType));
}
string strNewPath = Server.MapPath("~/upload/" + strFileType);
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + strNewPath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
query = "SELECT * FROM [Sheet1$]";
conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed) conn.Open();
cmd = new OleDbCommand(query, conn);
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
da.Dispose();
conn.Close();
conn.Dispose();
注意：如果使用上述的第一种方式先导出Excel，然后在导入Excel，会报错，因为第一种方式导出的不是标准的Excel，是html的方式导出的，第二种NPIO导出，这个是标准的Excel数据。