/// // / converts the data in the specified Excel file into a DataTable object, and the supplier is further processed /// /// // / public static System.Data.DataTable Import (string filePath) {System.Data.DataTable rs = new System.Data.DataTable (); bool canOpen = false; OleDbConnection conn = new OleDbConnection ( "Provider = Microsoft.jet.Oledb.4.0; " " Data Source = " " "extended Properties = /" excel 8.0; / "); try // Try data connection {conn.open CONN.CLOSE (); canopen = true;} catch {}
if (canOpen) {try // If the data connection can be opened into the attempts to read data {OleDbCommand myOleDbCommand = new OleDbCommand ( "SELECT * FROM [Sheet1 $]", conn); OleDbDataAdapter myData = new OleDbDataAdapter (myOleDbCommand); myData.Fill (RS); conn.close ();} catch // If the data connection can be opened but read 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 * FROM [" 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;} /// /// converts the data of the specified HTML string into a DataTable object - according to the special characters such as "
" /// /// < Param name = "tmphtml"> html string /// private statunication getDataBefromstrin G (String Tmpstr = TMPHTML; DATATABLE TB = New DataTable (); // Treat this string first, delete the first
before the last after the first INT INDEX = Tmpstr .Indexof (" -1) tmpstr = tmpstr.substring (index); else returnit; 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
