C data import and export Excel file and winForm export Execl summary

  • 2020-05-24 05:24:44
  • OfStack

1. The method of exporting Execl in asp.net:

There are two ways to export Execl in asp.net. One is to store the exported file under a folder on the server and then output the file address to the browser. One is to send the file output stream directly to the browser. When Response is output, \t delimited data, when execl is exported, it is equivalent to sorting, \n is equivalent to newline.

1. Output the whole html to execl

This method outputs all the contents of html, such as buttons, tables, pictures, etc. into Execl.


Response.Clear(); 
Response.Buffer= true; 
Response.AppendHeader("Content-Disposition","attachment;filename="+DateTime.Now.ToString("yyyyMMdd")+".xls"); 
Response.ContentEncoding=System.Text.Encoding.UTF8; 
Response.ContentType = "application/vnd.ms-excel"; 
this.EnableViewState = false; 

Here we use the ContentType property, whose default property is text/html. In this case, the output will be hypertext, that is, the common web page format to the client. If we change it to ms-excel, the output will be excel format, that is, output to the client in the form of a spreadsheet, then the browser will prompt you to download and save. ContentType also has the following attributes: image/JPEG; text/HTML; image/GIF; vnd. ms - excel/msword. Similarly, we can export (export) images, word documents, and so on. The following methods also use this property.

2. Export the data in the DataGrid control to Execl

Although the above method implements the export function, it also guides all the output information in html, such as buttons and paging box. And what we're going to export in 1 is data, data on the DataGrid control.


System.Web.UI.Control ctl=this.DataGrid1; 
//DataGrid1 Is the control you drag and drop on the form  
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls"); 
HttpContext.Current.Response.Charset ="UTF-8"; 
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default; 
HttpContext.Current.Response.ContentType ="application/ms-excel"; 
ctl.Page.EnableViewState =false; 
System.IO.StringWriter tw = new System.IO.StringWriter() ; 
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw); 
ctl.RenderControl(hw); 
HttpContext.Current.Response.Write(tw.ToString()); 
HttpContext.Current.Response.End(); 

If your DataGrid USES paging, it exports information about the current page, which means it exports information that is displayed in DataGrid. Not all of your select statements.

For convenience, it can be written as follows:


public void DGToExcel(System.Web.UI.Control ctl) 
{ 
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls"); 
HttpContext.Current.Response.Charset ="UTF-8"; 
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default; 
HttpContext.Current.Response.ContentType ="application/ms-excel"; 
ctl.Page.EnableViewState =false; 
System.IO.StringWriter tw = new System.IO.StringWriter() ; 
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw); 
ctl.RenderControl(hw); 
HttpContext.Current.Response.Write(tw.ToString()); 
HttpContext.Current.Response.End(); 
} 

Usage: DGToExcel (datagrid1);

3. Export the data in DataSet to Execl

With the above idea, is to export the information in the output (Response) client, so you can export. So you export the data from DataSet, which means you take the rows from the table in DataSet, Response, ms-excel, into the http stream, and you get OK. Note: the parameter ds should be DataSet with the data table filled in. The file name is the full name, including the suffix execl2006.xls


public void CreateExcel(DataSet ds,string FileName) 
{ 
HttpResponse resp; 
resp = Page.Response; 
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 
resp.AppendHeader("Content-Disposition", "attachment;filename="+FileName); 
string colHeaders= "", ls_item=""; 

// Defines both a table object and a row object DataSet Initializes its value  
DataTable dt=ds.Tables[0]; 
DataRow[] myRow=dt.Select();// Can be similar to dt.Select("id>10") To achieve the purpose of data filtering  
int i=0; 
int cl=dt.Columns.Count; 


// Gets the column headings in the data table, and between them \t Partition, finally 1 A carriage return character is added to each column heading  
for(i=0;i<cl;i++) 
{ 
if(i==(cl-1))// The last 1 The column, \n 
{ 
colHeaders +=dt.Columns[i].Caption.ToString() +"\n"; 
} 
else 
{ 
colHeaders+=dt.Columns[i].Caption.ToString()+"\t"; 
} 

} 
resp.Write(colHeaders); 
// to HTTP Writes the obtained data information to the output stream  

// Process data row by row  
foreach(DataRow row in myRow) 
{ 
// Current row data write HTTP Output stream, and null ls_item In order to downlink the data  
for(i=0;i<cl;i++) 
{ 
if(i==(cl-1))// The last 1 The column, \n 
{ 
ls_item +=row[i].ToString()+"\n"; 
} 
else 
{ 
ls_item+=row[i].ToString()+"\t"; 
} 

} 
resp.Write(ls_item); 
ls_item=""; 

} 
resp.End(); 
} 

4. Export dataview to execl

If you want to achieve a more variable or irregular row and column execl export, you can use this method.


public void OutputExcel(DataView dv,string str) 
{ 
//dv To output to Excel The data, str Is the title name  
GC.Collect(); 
Application excel;// = new Application(); 
int rowIndex=4; 
int colIndex=1; 

_Workbook xBk; 
_Worksheet xSt; 

excel= new ApplicationClass(); 

xBk = excel.Workbooks.Add(true); 

xSt = (_Worksheet)xBk.ActiveSheet; 

// 
// Get the title  
// 
foreach(DataColumn col in dv.Table.Columns) 
{ 
colIndex++; 
excel.Cells[4,colIndex] = col.ColumnName; 
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;// Set the header format to center alignment  
} 

// 
// Get the data in the table  
// 
foreach(DataRowView row in dv) 
{ 
rowIndex ++; 
colIndex = 1; 
foreach(DataColumn col in dv.Table.Columns) 
{ 
colIndex ++; 
if(col.DataType == System.Type.GetType("System.DateTime")) 
{ 
excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); 
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;// Set the date-type field format to center alignment  
} 
else 
if(col.DataType == System.Type.GetType("System.String")) 
{ 
excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString(); 
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;// Sets the character - type field format to center - aligned  
} 
else 
{ 
excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString(); 
} 
} 
} 
// 
// loading 1 A total line  
// 
int rowSum = rowIndex + 1; 
int colSum = 2; 
excel.Cells[rowSum,2] = " A combined "; 
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter; 
// 
// Sets the color of the selected part  
// 
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select(); 
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;// Set to light yellow, total 56 Kind of  
// 
// Gets the title of the entire report  
// 
excel.Cells[2,2] = str; 
// 
// Sets the header format for the entire report  
// 
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true; 
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22; 
// 
// Set the report table to the most suitable width  
// 
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select(); 
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit(); 
// 
// Set the title of the entire report to middle across columns  
// 
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select(); 
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; 
// 
// Draw the border  
// 
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1; 
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;// Make the left line bold  
xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;// Set the upper edge bold  
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;// Set the right line bold  
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;// Set the lower edge bold  
// 
// According to the effect  
// 
excel.Visible=true; 

//xSt.Export(Server.MapPath(".")+"\\"+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML); 
xBk.SaveCopyAs(Server.MapPath(".")+"\\"+this.xlfile.Text+".xls"); 

ds = null; 
xBk.Close(false, null,null); 

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(); 
string path = Server.MapPath(this.xlfile.Text+".xls"); 

System.IO.FileInfo file = new System.IO.FileInfo(path); 
Response.Clear(); 
Response.Charset="GB2312"; 
Response.ContentEncoding=System.Text.Encoding.UTF8; 
//  Add header information as " File download / Save as " The dialog box specifies the default file name  
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); 
//  Add header information, specify file size, and allow the browser to display the download progress  
Response.AddHeader("Content-Length", file.Length.ToString()); 

//  Specifies that is returned 1 Streams that cannot be read by the client must be downloaded  
Response.ContentType = "application/ms-excel"; 

//  Send the file stream to the client  
Response.WriteFile(file.FullName); 
//  Stop page execution  

Response.End(); 
} 


The above aspect will export the execl data and output the file stream directly to the browser. The following method is to first save it in a folder on the server and then send the file to the client. This allows you to persist the exported file for other purposes.

5. Export the execl file to the server and download it.

2. Method of exporting Execl from winForm:

1. Method 1:


public void Out2Excel(string sTableName,string url) 
{ 
Excel.Application oExcel=new Excel.Application(); 
Workbooks oBooks; 
Workbook oBook; 
Sheets oSheets; 
Worksheet oSheet; 
Range oCells; 
string sFile="",sTemplate=""; 
// 
System.Data.DataTable dt=TableOut(sTableName).Tables[0]; 

sFile=url+"\\myExcel.xls"; 
sTemplate=url+"\\MyTemplate.xls"; 
// 
oExcel.Visible=false; 
oExcel.DisplayAlerts=false; 
// define 1 A new workbook  
oBooks=oExcel.Workbooks; 
oBooks.Open(sTemplate,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing, Type.Missing); 
oBook=oBooks.get_Item(1); 
oSheets=oBook.Worksheets; 
oSheet=(Worksheet)oSheets.get_Item(1); 
// Named after the sheet 
oSheet.Name="Sheet1"; 

oCells=oSheet.Cells; 
// call dumpdata Procedure to import data into Excel in  
DumpData(dt,oCells); 
// save  
oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); 
oBook.Close(false, Type.Missing,Type.Missing); 
// exit Excel , and release the called COM resources  
oExcel.Quit(); 

GC.Collect(); 
KillProcess("Excel"); 
} 

private void KillProcess(string processName) 
{ 
System.Diagnostics.Process myproc= new System.Diagnostics.Process(); 
// Get all the open processes  
try 
{ 
foreach (Process thisproc in Process.GetProcessesByName(processName)) 
{ 
if(!thisproc.CloseMainWindow()) 
{ 
thisproc.Kill(); 
} 
} 
} 
catch(Exception Exc) 
{ 
throw new Exception("",Exc); 
} 
} 

2. Method 2:

protected void ExportExcel() 
{ 
gridbind(); 

if(ds1==null) return; 

string saveFileName=""; 
// bool fileSaved=false; 
SaveFileDialog saveDialog=new SaveFileDialog(); 
saveDialog.DefaultExt ="xls"; 
saveDialog.Filter="Excel file |*.xls"; 
saveDialog.FileName ="Sheet1"; 
saveDialog.ShowDialog(); 
saveFileName=saveDialog.FileName; 
if(saveFileName.IndexOf(":")<0) return; // It was called off  
// excelapp.Workbooks.Open (App.path & \\ Project schedule .xls) 


Excel.Application xlApp=new Excel.Application(); 
object missing=System.Reflection.Missing.Value; 


if(xlApp==null) 
{ 
MessageBox.Show(" Unable to create the Excel Object that may not be installed on your machine Excel"); 
return; 
} 
Excel.Workbooks workbooks=xlApp.Workbooks; 
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); 
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];// achieve sheet1 
Excel.Range range; 


string oldCaption=Title_label .Text.Trim (); 
long totalCount=ds1.Tables[0].Rows.Count; 
long rowRead=0; 
float percent=0; 

worksheet.Cells[1,1]=Title_label .Text.Trim (); 
// Written to the field  
for(int i=0;i<ds1.Tables[0].Columns.Count;i++) 
{ 
worksheet.Cells[2,i+1]=ds1.Tables[0].Columns.ColumnName; 
range=(Excel.Range)worksheet.Cells[2,i+1]; 
range.Interior.ColorIndex = 15; 
range.Font.Bold = true; 

} 
// Write the numerical  
Caption .Visible = true; 
for(int r=0;r<ds1.Tables[0].Rows.Count;r++) 
{ 
for(int i=0;i<ds1.Tables[0].Columns.Count;i++) 
{ 
worksheet.Cells[r+3,i+1]=ds1.Tables[0].Rows[r]; 
} 
rowRead++; 
percent=((float)(100*rowRead))/totalCount; 
this.Caption.Text= " Exporting data ["+ percent.ToString("0.00") +"%]..."; 
Application.DoEvents(); 
} 
worksheet.SaveAs(saveFileName,missing,missing,missing,missing,missing,missing,missing,missing); 

this.Caption.Visible= false; 
this.Caption.Text= oldCaption; 

range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds1.Tables[0].Rows.Count+2,ds1.Tables[0].Columns.Count]); 
range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null); 

range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; 
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous; 
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin; 

if(ds1.Tables[0].Columns.Count>1) 
{ 
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex=Excel.XlColorIndex.xlColorIndexAutomatic; 
} 
workbook.Close(missing,missing,missing); 
xlApp.Quit(); 
} 



3. Note:

Although they all implement the function of exporting execl, the code of the implementation is different in the programs of asp.net and winform. In asp.net, the data is read on the server side and output to the browser (client side) on the server side in the form of ms-execl. In winform, the data is read to the client (because the winform runtime is the client), then the office component installed by the client is called, and the data read is written in the execl workbook.


SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]); 
SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn); 
DataSet ds=new DataSet(); 
da.Fill(ds,"table1"); 
DataTable dt=ds.Tables["table1"]; 
string name=System.Configuration.ConfigurationSettings.AppSettings["downloadurl"].ToString()+DateTime.Today.ToString("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next(10000).ToString()+".csv";// Deposit to web.config In the downloadurl The specified path. The file format is the current date +4 A random number  
FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write); 
StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312")); 
sw.WriteLine(" Automatic numbering , The name , age "); 
foreach(DataRow dr in dt.Rows) 
{ 
sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]); 
} 
sw.Close(); 
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name)); 
Response.ContentType = "application/ms-excel";//  Specifies that is returned 1 Streams that cannot be read by the client must be downloaded  
Response.WriteFile(name); //  Send the file stream to the client  
Response.End();


Related articles: