Data Exports Excel File /// /// Exports data in DataTable to the specified Excel file /// /// Web page object < / param> /// contains DataTable objects of exported data /// Excel file name public static void export (System) .Web.UI.Page page, System.Data.DataTable tab, string FileName) {System.Web.HttpResponse httpResponse = page.Response; System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls. DataGrid (); dataGrid.DataSource = tab.DefaultView; dataGrid.AllowPaging = false; dataGrid.HeaderStyle.BackColor = System.Drawing.Color.Green; dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center; dataGrid.HeaderStyle.Font.Bold = true; dataGrid.DataBind (); httpResponse.AppendHeader ( "Content-Disposition", "attachment; filename =" HttpUtility.UrlEncode (fileName, System.Text.Encoding.UTF8)); //filename="*.xls " Httpresponse.contentencoding = system.text.Encoding.Getencoding ("GB2312"); httpresponse.contenttype = "Application / MS- excel "; System.IO.StringWriter tw = new System.IO.StringWriter (); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw); dataGrid.RenderControl (hw); string filePath = Page.Server.mappath ("..") "// files //" filename; system.io.streamwriter sw = system.io.file.createtext (filepath); sw.write (two totostring ()) SW.CLOSE ();
DownFile (httpResponse, FileName, filePath); httpResponse.End ();} private static bool DownFile (System.Web.HttpResponse Response, string fileName, string fullPath) {try {Response.ContentType = "application / octet-stream"; Response .AppendHeader ( "Content-Disposition", "attachment; filename =" HttpUtility.UrlEncode (fileName, System.Text.Encoding.UTF8) "; charset = GB2312"); System.IO.FileStream fs = System.IO. FILE.OPENREAD (FULLPATH); long flen = fs.length; int size = 102400; // Each 100k download data byte [] readdata = new byte [size]; // Specify the size of the buffer if (size> flen) SIZE = Convert.Toint32 (Flen); long fpos = 0; bool = false; while (! isend) {if ((fpos size)> flen) {size = convert.toint32 (flen-fpos); readdata = new Byte [size]; isend = true;} fs.read (readdata, 0, size); // reads a compressed block response.binaryWrite (READDATA); fpos = size;} fs.close (); system.io. File.delete (FullPath); Return True;} Catch {Return False;}} Data in the Excel file is converted to DataTable /// /// convert the data in the specified Excel file into a DataTable object, the supplier is further processed /// ///
Try // Try data connection {conn.open (); conn.close (); canopen = true;} catch {} If the data connection can be opened, try reading data {OLEDBCommand myoledbcommand = New OLEDBCOMMAND ("Select * from [Sheet1 $]", CONN); OLEDBDataAdapter MyData = New OLEDBDataAdapter (MyData.Fill (RS); conn.close ();} catCh // If the data connection can be opened but read If the data fails, the name of the worksheet is extracted from the file, and then read the data {string sheetname = getSheetName (FilePath); if (SheetName.Length> 0) {OLEDBCommand myoledbcommand = new oledbcommand ("SELECT * FROMMAND" sheetName "$]", conn); OleDbDataAdapter myData = new OleDbDataAdapter (myOleDbCommand); myData.Fill (rs); conn.Close ();}}} else {System.IO.StreamReader tmpStream = File.OpenText (filePath); String Tmpstr = TmpStream.readToEnd (); TmpStream.close (); rs = getDataTableFromString (tmpstr); tmpstr = "";} Return RS;} /// /// will specify data of the HTML string to convert DataTable object - Processing /// /// HTML word according to special characters such as
" String /// private static datatable getdataablefromstring (string tmpstr = tmphtml; dataable tb = new data (); // Treat this string first, delete the first Part of the previous before the previous before the previous , INT INDEX = TmpStr. IndexOf (" -1) Tmpstr = TmpStr.Substring (INDEX); Else Return TB;
Index = tmpstr.lastIndexof (""); if (index> -1) Tmpstr = tmpstr.substring (0, Index 5); Else Return TB;
// If the original string contains the separator "^" first replaces the IF (TmpStr.indexof (Separator.Tostring ())> - 1) {EXISTSSPARATOR = true; tmpstr = tmpstr.Replace ("^", "^ $ & ^");} // first remove string in "" ("", "^"). Split (separator);
For (int i = 0; i
String tmpstri = tmprow [i]; if (tmpstri.indexof (" - 1) {tmpstri = tmpstri.substring (TmpStri.indexOf (" Tmpstri.indexof ("> ")) {tmpstri = tmpstri.replace (" "," ^ "); string [] TMPField = tmpstri.split (separator); for (int J = 0; j "); index = tmpfield [J ] .LastIndexof (">") 1; if (index> 0) {string field = tmpfield [j] .substring (INDEX, TMPFIELD [J] .length-index); if (existssparator) field = field.replace "^ $ & ^", "^"); If (i == 0) {string tmpfieldname = field; int SN = 1; while (tb.columns.contains (tmpfieldname)) {TMPFIELDNAME = Field sn.toTostring ); Sn = 1;} Tb.columns.add (tmpfieldname);} else {newrow [j] = field;}} // end of if (index> 0)}
IF (i> 0) TB.Rows.Add (new);}}}
Tb.acceptchanges (); return TB;}
/// /// From the specified HTML string, the specified object /// /// HTML string //// Items that need to be removed - for example, "", "" and ">" /// public static string RemoveString (string tmpHtml, string remove) {tmpHtml = tmpHtml.Replace (remove.Replace ( "<", " /// only for method RemoveString () Using /// /// private static string removestringhead (String TmpHTML, String Remove) {// For the convenience of comments, assume that the input parameter remove = "REMOVE.LENGTH <1) return TMPHTML; // Parameter Remove is empty: Non-processes return IF ((REMOVE.SUBSTRING (0, 1)! =" < ") || (Remove.Substring (REMOVE.LENGTH-1)! =">)) Return TMPHTML; // Parameter Remove is not : Do not process returns = TMPHTML.INDEXOF (Remove. " Replace (">", "")); / / Find " -1) {string tmpright = tmphtml.substring (indexs, tmphtml.length-indexs); Indexe = TMPRIGHT.INDEXOF (">"); if (indexe> -1) TMPHTML = TMPHTML.S Ubstring (0, indexs) TMPHTML.SUBSTRING (Indexs Indexe 1); if (TMPHTML.INDEXOF (REMOVE.REPLACE (">)> - 1) TMPHTML = RemoveStringhead (TMPHTML, REMOVE); Return TMPHTML;
/// /// Read the name of the first worksheet in the specified Excel file /// /// /// private static string GetSheetName (string filePath) {string sheetName = ""; System.IO.FileStream tmpStream = File.OpenRead (filePath); byte [] fileByte = new byte [tmpStream.Length]; tmpStream.Read (FileByte, 0, FileByte.Length); TmpStream.close (); byte [] TMPBYTE = New Byte [] {Convert.Tobyte (11), Convert.Tobyte (0), Convert.Tobyte (0), Convert.Tobyte (0), Convert.Tobyte (0), Convert.Tobyte (0), Convert.Tobyte (0), Convert.Tobyte (11), Convert.Tobyte (0), Convert.Tobyte 0), Convert.Tobyte (0), Convert.Tobyte (0), Convert.Tobyte (0), Convert.Tobyte (0), Convert.Tobyte (30), Convert.Tobyte (16 ), Convert.tobyte (0), convert.tobyte (0)}; int index = getSheetIndex (filebyte, tmpbyte); if (index> -1) {
Index = 16 12; system.collections.arraylist sheetnamelist = new system.collections.arrayList (); for (int i = index; i /// only available GetSheetName () use /// /// private static int getSheetIndex (byte [] FindTarget, Byte [] FindItem) {int index = -1;
int FindItemLength = FindItem.Length; if (FindItemLength <1) return -1; int FindTargetLength = FindTarget.Length; if ((FindTargetLength-1) -1; i--) {system.collections.arraylist tmplist = new system.collections.arrayList (); int Find = 0; for (int J = 0; j
