ASP. NET Excel download template import export operations
- 2021-08-03 10:06:07
- OfStack
This article introduces ASP. NET Excel download template, import, export operation, for your reference, the specific content is as follows
1. Download template functionality
protected void btnDownload_Click(object sender, EventArgs e)
{
var path = Server.MapPath(("upfiles\\") + "test.xlt"); //upfiles- Folder test.xlt- Documents
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, specifying the default file name
Response.AddHeader("Content-Length", file.Length.ToString());// Show download progress
Response.ContentType = "application/ms-excel"; // Specifies that the returned is 1 Streams that cannot be read by the client must be downloaded
Response.WriteFile(file.FullName); // Send the file stream to the client
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
catch (Exception ex)
{
Response.Write("<script>alert(' Errors :" + ex.Message + ", Please contact the administrator as soon as possible ')</script>");
}
}
2. 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 1 Files
{
Response.Write("<script>alert(' Please choose Excel Files! ')</script>");// Click the import button without uploading
return;
}
string isXls = Path.GetExtension(FileUpload1.FileName).ToString().ToLower();// Get the extension of the file
var extenLen = isXls.Length;
if (!isXls.Contains(".xls")) // Judge whether Yes excel Documents
{
Response.Write("<script>alert(' You can only select Excel Files! ')</script>");
return;
}
string filename = FileUpload1.FileName; // Get Excle Filename
string savePath = Server.MapPath(("upfiles\\") + filename);//Server.MapPath Get the relative path of virtual server
string savePath2 = Server.MapPath(("upfiles\\"));
if (!Directory.Exists(savePath2)) // If it doesn't exist upfiles Folder is created
{
Directory.CreateDirectory(savePath2);
}
FileUpload1.SaveAs(savePath); //SaveAs Save the uploaded file contents on the server
var ds = ExcelSqlConnection(savePath, filename); // Will Excel Turn into DataSet
var dtRows = ds.Tables[0].Rows.Count;
var dt = ds.Tables[0];
if (dtRows == 0)
{
Response.Write("<script>alert('Excel Table has no data! ')</script>");
return;
}
try
{
for(int i = 0; i < dt.Rows.Count; i++)
{
string ve = dt.Rows[i][" Car number "].ToString();
if (string.IsNullOrEmpty(ve)) // Because the car number in the database cannot be empty So skip this row if the car number in the table is empty
{
continue;
}
// Save to the database in your own way ADO/EF/...
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("<script>alert('" + ex.Message + "')</script>");
}
}
private DataSet ExcelSqlConnection(string savePath, string tableName)
{
//string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
string strCon = "Provider=Microsoft.Ace.OLEDB.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //HDR=YES Excel Article 1 of the document 1 Rows are column names, not data IMEX=1 Data type conflicts can be avoided
var excelConn = new OleDbConnection(strCon);
try
{
string strCom = string.Format("SELECT * FROM [Sheet1$]");
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("<script>alert('" + ex.Message + "')</script>");
return null;
}
}
3. Export data to Excel
The plug-in adopts MyXLS.
Most of the following codes need not be changed.
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 name
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 No. 1 1 Line number 1 To 2 Column display TEST
sheet.AddMergeArea(area);
cellTitle.Font.Height = 20 * 20;
cellTitle.Font.Bold = true;// Set the font of the header line to bold
cellTitle.Font.FontFamily = FontFamilies.Roman;// Set the font of the header row to FontFamilies.Roman
cellTitle.HorizontalAlignment = HorizontalAlignments.Centered;
area = new MergeArea(2, 2, 1, 1);
cellTitle = cells.AddValueCell(2, 1, " Car number "); // No. 1 2 Line number 1 Column Display car 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; i++)
{
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-Server cannot set content type after sending HTTP header
Add the ability of 'Global Refresh' to the export button. The example in this article is done by aspx in
<
asp:UpdatePanel
>
Add the following code to the tag
<Triggers>
<%--<asp:AsyncPostBackTrigger ControlID="" />--%> <%-- Local refresh Value refresh UpdatePanel Internal --%>
<asp:PostBackTrigger ControlID="btnExport" /> <%-- Refresh All --%> <%--2016 Year 7 Month 1 Day To solve the problem of clicking the export button to report an error, "the server cannot send HTTP Error setting content type after header " --%>
</Triggers>