Shulou(Shulou.com)06/03 Report--

This article mainly introduces the ASP.NET Excel download template, import, export operation example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian with you to understand.

1. Download template function

Protected void btnDownload_Click (object sender, EventArgs e) {var path = Server.MapPath (("upfiles\") + "test.xlt"); / / upfiles- folder test.xlt- file var name= "test.xlt"; try {var file = new FileInfo (path); Response.Clear (); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.AddHeader ("Content-Disposition", "attachment; filename=" + Server.UrlEncode (name)) / / header information, specify the default file name Response.AddHeader ("Content-Length", file.Length.ToString ()); / / display download progress Response.ContentType = "application/ms-excel"; / / specify that a stream that cannot be read by the client is returned and must be downloaded Response.WriteFile (file.FullName); / / send the file stream to the client HttpContext.Current.ApplicationInstance.CompleteRequest () } catch (Exception ex) {Response.Write ("alert ('error:" + ex.Message + ", please contact the administrator as soon as possible);}}

two。 Import data

Excel data is imported into the database.

Protected void btnImport_Click (object sender, EventArgs e) {if (FileUpload1.HasFile = = false) / / determine whether to include a file {Response.Write ("alert ('Please select an Excel file!')") ; / / Click the import button return;} string isXls = Path.GetExtension (FileUpload1.FileName). ToString (). ToLower () before uploading; / / get the file extension var extenLen = isXls.Length; if (! isXls.Contains (".xls")) / / determine whether it is an excel file {Response.Write ("alert ('only Excel files can be selected!')") ; return;} string filename = FileUpload1.FileName; / / get the Excle file name string savePath = Server.MapPath (("upfiles\\") + filename); / / Server.MapPath get the virtual server relative path string savePath3 = Server.MapPath (("upfiles\")); if (! Directory.Exists (savePath3)) / / create {Directory.CreateDirectory (savePath3);} FileUpload1.SaveAs (savePath) if the upfiles folder does not exist / / SaveAs saves the uploaded file contents on the server var ds = ExcelSqlConnection (savePath, filename); / / converts Excel to DataSet var dtRows = ds.Tables [0] .Rows.Count; var dt = ds.Tables [0]; if (dtRows = = 0) {Response.Write ("alert ('Excel table has no data!')") ; return;} try {for (int I = 0; I < dt.Rows.Count; iTunes +) {string ve = dt.Rows [I] ["car number"] .ToString (); if (string.IsNullOrEmpty (ve)) / / because the car number in the table cannot be empty, the line {continue;} / / is saved in the database ADO/EF/... in your own way. Var model = new TEST (); / / entity model.id = 1; model.ve = ve; model.name = dt.Rows [I] ["name"] .ToString (); model.Update ();} catch (Exception ex) {Response.Write ("alert ('" + ex.Message + ")");}} private DataSet ExcelSqlConnection (string savePath, string tableName) {/ / string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + " Extended Properties='Excel 8.0 var excelConn new OleDbConnection IMEX=1 1'"; string strCon =" Provider=Microsoft.Ace.OLEDB.12.0; "+" data source= "+ savePath +"; HDR 12.0; HDR=Yes; IMEX=1' "; / / the first line of the HDR file is column names instead of data, but data type conflicts are avoided var excelConn = new OleDbConnection (strCon); try {string strCom = string.Format (" SELECT * FROM [IMEX $] "); excelConn.Open (); OleDbDataAdapter myCommand = new OleDbDataAdapter (strCom, excelConn) DataSet ds = new DataSet (); myCommand.Fill (ds, "[" + tableName + "$]"); excelConn.Close (); return ds;} catch (Exception) {excelConn.Close (); / / Response.Write ("alert ('" + ex.Message + "") "); return null;}}

3. Export data to Excel

The plug-in adopts MyXLS.

Most of the following code is basically unchanged.

Private void Export () {XlsDocument xls = new XlsDocument (); org.in2bits.MyXls.Cell cell; int rowIndex = 2; xls.FileName = DateTime.Now.ToString (). Replace ("-", "). Replace (": ","). Replace (",") + HttpUtility.UrlEncode ("TEST") + ".xls"; / / TEST to change Worksheet sheet = xls.Workbook.Worksheets.AddNamed ("TEST"); / / status bar title org.in2bits.MyXls.Cells cells = sheet.Cells # region header MergeArea area = new MergeArea (1, 1, 1, 2); / / MergeArea (int rowMin, int rowMax, int colMin, int colMax) org.in2bits.MyXls.Cell cellTitle = cells.AddValueCell (1, 1, "TEST"); / / Excel first row 1 to 2 columns show TEST sheet.AddMergeArea (area); cellTitle.Font.Height = 20 * 20; cellTitle.Font.Bold = true;// sets the font of the header row to bold cellTitle.Font.FontFamily = FontFamilies.Roman / / set the font of the header line as FontFamilies.Roman cellTitle.HorizontalAlignment = HorizontalAlignments.Centered; area = new MergeArea (2, 2, 1, 1); cellTitle = cells.AddValueCell (2, 1, "license plate number"); / / the first column of the second row shows the license plate number sheet.AddMergeArea (area); cellTitle.Font.Bold = true; cellTitle.Font.Height = 16 * 16; cellTitle.Font.FontFamily = FontFamilies.Roman; cellTitle.HorizontalAlignment = HorizontalAlignments.Centered; cellTitle.VerticalAlignment = VerticalAlignments.Centered; cellTitle.TopLineStyle = 1; cellTitle.BottomLineStyle = 1 CellTitle.LeftLineStyle = 1; cellTitle.RightLineStyle = 1; area = new MergeArea (2,2,2,2); cellTitle = cells.AddValueCell (2,2, "name"); sheet.AddMergeArea (area); cellTitle.Font.Bold = true; cellTitle.Font.Height = 16 * 16; cellTitle.Font.FontFamily = FontFamilies.Roman; cellTitle.HorizontalAlignment = HorizontalAlignments.Centered; cellTitle.VerticalAlignment = VerticalAlignments.Centered; cellTitle.TopLineStyle = 1; cellTitle.BottomLineStyle = 1; cellTitle.LeftLineStyle = 1; cellTitle.RightLineStyle = 1; # endregion var list = GetList () / / get data for (int I = 0; I < list.Count; iTunes +) {rowIndex++; cell = cells.AddValueCell (rowIndex, 1, list [I] .Vehicleno); / / car number cell.TopLineStyle = 1; cell.BottomLineStyle = 1; cell.LeftLineStyle = 1; cell.RightLineStyle = 1; cell = cells.AddValueCell (rowIndex, 2, list [I] .Name); / / name cell.TopLineStyle = 1; cell.BottomLineStyle = 1; cell.LeftLineStyle = 1; cell.RightLineStyle = 1;} xls.Send ();}

4. Error-the "Microsoft.ACE.OLEDB.12.0" provider is not registered on the local computer

01. Replace the platform with X86

02. Install AccessDatabaseEngine.exe (click to download)

5. Error-the server could not set the content type after sending the HTTP header

Add the ability to 'global refresh' to the export button. The example in this article is done by aspx to add the following code to the tag

Thank you for reading this article carefully. I hope the article "sample Analysis of Excel download template, Import and Export of ASP.NET" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.