excel导入和导出
protected void btnUpload_Click(object sender, EventArgs e) { try {
if (hfdIsUpload.Value.Trim() == "") { hfdIsUpload.Value = "Upload"; fldUpload.Visible = true; } else { if (fldUpload.PostedFile.FileName.Length == 0) { UIHelper.AlertMessage(uplMain, "Please Select File!"); return; } DataSet ds = new DataSet(); string sheet = this.fldUpload.PostedFile.FileName.Substring(this.fldUpload.PostedFile.FileName.LastIndexOf("\\") + 1); sheet = sheet.Substring(0, sheet.IndexOf(".")); string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fldUpload.PostedFile.FileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; string str = "SELECT * FROM [Sheet1$]";
OleDbConnection myconn = new OleDbConnection(connectionstring); myconn.Open(); OleDbDataAdapter adp = new OleDbDataAdapter(str, myconn); adp.Fill(ds, sheet); myconn.Close(); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { VSDataTableGv = ds.Tables[0]; gvDetail.DataSource = VSDataTableGv; gvDetail.DataBind(); } hfdIsUpload.Value = ""; fldUpload.Visible = false; }
} catch (Exception ex) { Com.Auo.SSP3g.Logs.Logger.GetLogger(System.Reflection.MethodBase.GetCurrentMethod()).Error(ex.ToString()); UIHelper.AlertMessage(this, ex.Message); } }
protected void btnDownload_Click(object sender, EventArgs e) { //StringBuilder sb = new StringBuilder(); //StringWriter sw = new StringWriter(sb); //HtmlTextWriter htw = new HtmlTextWriter(sw);
//Page page = new Page(); //HtmlForm form = new HtmlForm(); //gdvHidden.DataSource = VSDataTableGv; //gdvHidden.DataBind();
//gdvHidden.EnableViewState = false;
//page.EnableEventValidation = false;
//page.DesignerInitialize();
//page.Controls.Add(form); //form.Controls.Add(gdvHidden);
//page.RenderControl(htw);
//Response.Clear(); //Response.Buffer = true; //Response.ContentType = "application/vnd.ms-excel"; //Response.AddHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_Detail.xls"); //Response.Charset = "UTF-8"; //Response.ContentEncoding = Encoding.Default; //Response.Write(sb.ToString()); //Response.End();
//导出到execl try { ////实例化一个Excel.Application对象 //Excel.Application excel = new Excel.Application();
////让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写 //excel.Visible = false;
////新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错 //excel.Application.Workbooks.Add(true);
////生成Excel中列头名称 //for (int i = 0; i < VSDataTableGv.Columns.Count; i++) //{ // excel.Cells[1, i + 1] = VSDataTableGv.Columns[i].ColumnName;//输出DataGridView列头名 //}
////把DataGridView当前页的数据保存在Excel中 //if (VSDataTableGv.Rows.Count > 0) //{ // for (int i = 0; i < VSDataTableGv.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完 // { // for (int j = 0; j < VSDataTableGv.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完 // { // string str = VSDataTableGv.Rows[i][j].ToString(); // excel.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制 // } // } //} ////设置禁止弹出保存和覆盖的询问提示框 //excel.DisplayAlerts = false; //excel.AlertBeforeOverwriting = false;
////保存工作簿，值为false会报错 //excel.Application.Workbooks.Add(true).Save(); ////保存excel文件 //excel.Save("D:" + "\\ttt.xls");
////确保Excel进程关闭 //excel.Quit(); //excel = null;
//string s = ""; //Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel._Workbook book; //Microsoft.Office.Interop.Excel._Worksheet sheet; //book = (Microsoft.Office.Interop.Excel._Workbook)app.Workbooks.Open("d:\\test.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //sheet = (Microsoft.Office.Interop.Excel._Worksheet)book.Sheets[1];
////for (int i = 0; i < VSDataTableGv.Rows.Count; i++) ////{ //try //{ // if (VSDataTableGv.Rows.Count > 0) // { // ////生成Excel中列头名称 // for (int i = 0; i < VSDataTableGv.Columns.Count; i++) // { // excel.Cells[1, i + 1] = VSDataTableGv.Columns[i].ColumnName;//输出DataGridView列头名 // }
// for (int i = 0; i < VSDataTableGv.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完 // { // for (int j = 0; j < VSDataTableGv.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完 // { // string str = VSDataTableGv.Rows[i][j].ToString(); // sheet.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制 // } // } // } // //sheet.Cells[i + 3, "A"] = ""; // //sheet.Cells[i + 3, "B"] = VSDataTableGv.Rows[0]; // //sheet.Cells[i + 3, "C"] = dv[i]["B"].ToString().Trim(); //} //catch (Exception ex) //{ // HttpContext.Current.Response.Write("<script language='javascript'>alert('" + ex.Message + "')</script>"); // book.Close(null, null, null); // app.Workbooks.Close(); // app.Quit(); // System.Runtime.InteropServices.Marshal.ReleaseComObject(app); // System.Runtime.InteropServices.Marshal.ReleaseComObject(book); // System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); // sheet = null; // book = null; // app = null; // GC.Collect(); // HttpContext.Current.Response.Write("<script language='javascript'>alert('导出失败！')</script>"); // return; //}
////} //book.Save(); //book.Close(null, null, null); //app.Workbooks.Close(); //app.Quit(); //System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //System.Runtime.InteropServices.Marshal.ReleaseComObject(book); //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); //sheet = null; //book = null; //app = null; //GC.Collect(); //GC.Collect(); //GC.Collect(); //HttpContext.Current.Response.Write("<script language='javascript'>alert('导出成功！')</script>"); //HttpContext.Current.Response.Write("<script language='javascript'>window.open('../Template_temp/ExportTA.xls','_bank')</script>");
GC.Collect(); Excel._Application excel;
Excel._Workbook xBk; Excel._Worksheet xSt;
excel = new ExcelApplication(); xBk = excel.Workbooks.Add(true); xSt = (Excel._Worksheet)xBk.ActiveSheet;
////生成Excel中列头名称 for (int i = 0; i < VSDataTableGv.Columns.Count; i++) { xSt.Cells[1, i + 1] = VSDataTableGv.Columns[i].ColumnName;//输出DataGridView列头名 }
////把DataGridView当前页的数据保存在Excel中 if (VSDataTableGv.Rows.Count > 0) { for (int i = 0; i < VSDataTableGv.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完 { for (int j = 0; j < VSDataTableGv.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完 { string str = VSDataTableGv.Rows[i][j].ToString(); xSt.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制 } } }
//显示效果 excel.Visible = false; xBk.SaveAs("d:\\TEST.xls ", Excel.XlFileFormat.xlExcel8, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); } catch (Exception ex) { Com.Auo.SSP3g.Logs.Logger.GetLogger(System.Reflection.MethodBase.GetCurrentMethod()).Error(ex.ToString()); UIHelper.AlertMessage(this, ex.Message); } }
if (hfdIsUpload.Value.Trim() == "") { hfdIsUpload.Value = "Upload"; fldUpload.Visible = true; } else { if (fldUpload.PostedFile.FileName.Length == 0) { UIHelper.AlertMessage(uplMain, "Please Select File!"); return; } DataSet ds = new DataSet(); string sheet = this.fldUpload.PostedFile.FileName.Substring(this.fldUpload.PostedFile.FileName.LastIndexOf("\\") + 1); sheet = sheet.Substring(0, sheet.IndexOf(".")); string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fldUpload.PostedFile.FileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; string str = "SELECT * FROM [Sheet1$]";
OleDbConnection myconn = new OleDbConnection(connectionstring); myconn.Open(); OleDbDataAdapter adp = new OleDbDataAdapter(str, myconn); adp.Fill(ds, sheet); myconn.Close(); if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { VSDataTableGv = ds.Tables[0]; gvDetail.DataSource = VSDataTableGv; gvDetail.DataBind(); } hfdIsUpload.Value = ""; fldUpload.Visible = false; }
} catch (Exception ex) { Com.Auo.SSP3g.Logs.Logger.GetLogger(System.Reflection.MethodBase.GetCurrentMethod()).Error(ex.ToString()); UIHelper.AlertMessage(this, ex.Message); } }
protected void btnDownload_Click(object sender, EventArgs e) { //StringBuilder sb = new StringBuilder(); //StringWriter sw = new StringWriter(sb); //HtmlTextWriter htw = new HtmlTextWriter(sw);
//Page page = new Page(); //HtmlForm form = new HtmlForm(); //gdvHidden.DataSource = VSDataTableGv; //gdvHidden.DataBind();
//gdvHidden.EnableViewState = false;
//page.EnableEventValidation = false;
//page.DesignerInitialize();
//page.Controls.Add(form); //form.Controls.Add(gdvHidden);
//page.RenderControl(htw);
//Response.Clear(); //Response.Buffer = true; //Response.ContentType = "application/vnd.ms-excel"; //Response.AddHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_Detail.xls"); //Response.Charset = "UTF-8"; //Response.ContentEncoding = Encoding.Default; //Response.Write(sb.ToString()); //Response.End();
//导出到execl try { ////实例化一个Excel.Application对象 //Excel.Application excel = new Excel.Application();
////让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写 //excel.Visible = false;
////新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错 //excel.Application.Workbooks.Add(true);
////生成Excel中列头名称 //for (int i = 0; i < VSDataTableGv.Columns.Count; i++) //{ // excel.Cells[1, i + 1] = VSDataTableGv.Columns[i].ColumnName;//输出DataGridView列头名 //}
////把DataGridView当前页的数据保存在Excel中 //if (VSDataTableGv.Rows.Count > 0) //{ // for (int i = 0; i < VSDataTableGv.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完 // { // for (int j = 0; j < VSDataTableGv.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完 // { // string str = VSDataTableGv.Rows[i][j].ToString(); // excel.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制 // } // } //} ////设置禁止弹出保存和覆盖的询问提示框 //excel.DisplayAlerts = false; //excel.AlertBeforeOverwriting = false;
////保存工作簿，值为false会报错 //excel.Application.Workbooks.Add(true).Save(); ////保存excel文件 //excel.Save("D:" + "\\ttt.xls");
////确保Excel进程关闭 //excel.Quit(); //excel = null;
//string s = ""; //Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel._Workbook book; //Microsoft.Office.Interop.Excel._Worksheet sheet; //book = (Microsoft.Office.Interop.Excel._Workbook)app.Workbooks.Open("d:\\test.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, //Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //sheet = (Microsoft.Office.Interop.Excel._Worksheet)book.Sheets[1];
////for (int i = 0; i < VSDataTableGv.Rows.Count; i++) ////{ //try //{ // if (VSDataTableGv.Rows.Count > 0) // { // ////生成Excel中列头名称 // for (int i = 0; i < VSDataTableGv.Columns.Count; i++) // { // excel.Cells[1, i + 1] = VSDataTableGv.Columns[i].ColumnName;//输出DataGridView列头名 // }
// for (int i = 0; i < VSDataTableGv.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完 // { // for (int j = 0; j < VSDataTableGv.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完 // { // string str = VSDataTableGv.Rows[i][j].ToString(); // sheet.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制 // } // } // } // //sheet.Cells[i + 3, "A"] = ""; // //sheet.Cells[i + 3, "B"] = VSDataTableGv.Rows[0]; // //sheet.Cells[i + 3, "C"] = dv[i]["B"].ToString().Trim(); //} //catch (Exception ex) //{ // HttpContext.Current.Response.Write("<script language='javascript'>alert('" + ex.Message + "')</script>"); // book.Close(null, null, null); // app.Workbooks.Close(); // app.Quit(); // System.Runtime.InteropServices.Marshal.ReleaseComObject(app); // System.Runtime.InteropServices.Marshal.ReleaseComObject(book); // System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); // sheet = null; // book = null; // app = null; // GC.Collect(); // HttpContext.Current.Response.Write("<script language='javascript'>alert('导出失败！')</script>"); // return; //}
////} //book.Save(); //book.Close(null, null, null); //app.Workbooks.Close(); //app.Quit(); //System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //System.Runtime.InteropServices.Marshal.ReleaseComObject(book); //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); //sheet = null; //book = null; //app = null; //GC.Collect(); //GC.Collect(); //GC.Collect(); //HttpContext.Current.Response.Write("<script language='javascript'>alert('导出成功！')</script>"); //HttpContext.Current.Response.Write("<script language='javascript'>window.open('../Template_temp/ExportTA.xls','_bank')</script>");
GC.Collect(); Excel._Application excel;
Excel._Workbook xBk; Excel._Worksheet xSt;
excel = new ExcelApplication(); xBk = excel.Workbooks.Add(true); xSt = (Excel._Worksheet)xBk.ActiveSheet;
////生成Excel中列头名称 for (int i = 0; i < VSDataTableGv.Columns.Count; i++) { xSt.Cells[1, i + 1] = VSDataTableGv.Columns[i].ColumnName;//输出DataGridView列头名 }
////把DataGridView当前页的数据保存在Excel中 if (VSDataTableGv.Rows.Count > 0) { for (int i = 0; i < VSDataTableGv.Rows.Count; i++)//控制Excel中行，上下的距离，就是可以到Excel最下的行数，比数据长了报错，比数据短了会显示不完 { for (int j = 0; j < VSDataTableGv.Columns.Count; j++)//控制Excel中列，左右的距离，就是可以到Excel最右的列数，比数据长了报错，比数据短了会显示不完 { string str = VSDataTableGv.Rows[i][j].ToString(); xSt.Cells[i + 2, j + 1] = "'" + str;//i控制行，从Excel中第2行开始输出第一行数据，j控制列，从Excel中第1列输出第1列数据，"'" +是以string形式保存，所以遇到数字不会转成16进制 } } }
//显示效果 excel.Visible = false; xBk.SaveAs("d:\\TEST.xls ", Excel.XlFileFormat.xlExcel8, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); } catch (Exception ex) { Com.Auo.SSP3g.Logs.Logger.GetLogger(System.Reflection.MethodBase.GetCurrentMethod()).Error(ex.ToString()); UIHelper.AlertMessage(this, ex.Message); } }
“江南一品绣”床上用品淘宝店：http://xiaomiwozhidao.taobao.com
