C # Operations Excel save to DataTable method

Example 1

Npoi is a. NET version of the POI project. POI is an Open-source Java project that reads and writes Microsoft OLE2 component documents such as Excel, Word, and so on.


Npoi Download Address: http://npoi.codeplex.com/


Npoi reads Excel into a Datatable, easy code:


Public DataTable Upload (string path)


{


DataTable dt = new DataTable ();


using (FileStream fs = new FileStream (path, FileMode.Open, FileAccess.Read))//Open XLS file


{


String fileext = System.IO.Path.GetExtension (Path);


Iworkbook wk;


if (Fileext = ". xls")


{


Excel 03


wk = new Hssfworkbook (FS);


}


else if (Fileext = ". xlsx")


{


Excel 07


wk = new Xssfworkbook (FS);


}


Else


{


return null;


}


Isheet sheet = wk.   Getsheetat (0); Read the first Sheel table data


System.Collections.IEnumerator rows = Sheet. Getrowenumerator ();


while (rows. MoveNext ())


{


IRow row = (irow) rows. Current;


DataRow dr = dt. NewRow ();


for (int i = 0; i < row. Lastcellnum; i++)


{


Icell cell = row. Getcell (i);


if (cell = = null)


{


Dr[i] = null;


}


Else


{


if (dt. Columns.count < i + 1)


{


String colname = Convert.tochar (((int) ' A ') + i). ToString ()//has limits up to Z columns


Dt. Columns.Add (colname);


Lable1. Content = colname;


Dr[i] = cell. ToString ();


}


Dr[i] = cell. ToString ();


}


}


DT = IMPORTDT (sheet,0,true);


Dt. Rows.Add (DR);


}


wk = null;


sheet = null;


return DT;


}


}

Three ways to read Excel

---------------------------------------------------Method One

<summary>


Parse Excel, return to DataTable


</summary>


<param name= "FileName" ></param>


<returns></returns>


public static System.Data.DataTable Impexcel (String fileName)


{


System.Data.DataTable dt = new System.Data.DataTable ();


Try


{


Microsoft.Office.Interop.Excel.Application app;


Workbooks WBS;


Worksheet ws;


App = new Microsoft.Office.Interop.Excel.Application ();


WBS = App. workbooks;


Wbs. ADD (FileName);


WS = (worksheet) app. Worksheets.get_item (1);


int rows = ws. UsedRange.Rows.Count;


int columns = ws. UsedRange.Columns.Count;


String bookname = ws. Name;


DT = Loaddatafromexcel (FilePath, BookName). Tables[0];


for (int i = 1; i < rows + 1; i++)


//{


DataRow dr = dt. NewRow ();


for (int j = 1; j <= columns; j + +)


//    {

_excel.range Range = Ws.get_range (app. Cells[i, J], app. Cells[i, j]);


Range. Select ();


if (i = = 1)


Dt. Columns.Add ("Columns" + j);/dt. Columns.Add (app. ActiveCell.Text.ToString ()) can directly use the first row as the column name, after the single merged cells, read out the same column name will be error, so it is best not to


DR[J-1] = app. ActiveCell.Text.ToString ();


//    }


Dt. Rows.Add (DR);


//}

NEWDT = dt. Clone ();


for (int i = 0; i < dt. Rows.Count; i++)


//{


if (dt. ROWS[I][5]. ToString ()!= "" && dt. ROWS[I][6]. ToString ()!= "" && dt. ROWS[I][7]. ToString ()!= "" && dt. ROWS[I][8]. ToString ()!= "" | | i = = 0)


Newdt. ImportRow (dt. Rows[i]);


//}


KillProcess (APP);


return DT;


}


catch (Exception ex)


{


MessageBox.Show ("Data-bound Excel failed!") Reason for failure: "+ex." message, "Hint information", MessageBoxButtons.OK, MessageBoxIcon.Information);


return DT;


}


}

----------------------------------------------------Method Two

<summary>


Parsing Excel


</summary>


<param name= "FilePath" ></param>


<param name= "Name" ></param>


<returns></returns>


public static DataSet Loaddatafromexcel (string filePath, string name)


{


Try


{


String strconn;


strconn = "Provider = microsoft.jet.oledb.4.0; Data Source = "+ FilePath +"; Extended Properties=excel 8.0 ";


strconn = @ "Provider=microsoft.ace.oledb.12.0;data source=" + FilePath + "; Extended properties= ' Excel 12.0 Xml; Hdr=yes;imex=1 ' ";


OleDbConnection oleconn = new OleDbConnection (strconn);


Oleconn.open ();


String sql = "SELECT * FROM [" + Name + "$]"; change sheet name, such as Sheet2, etc.


OleDbDataAdapter oledaexcel = new OleDbDataAdapter (sql, oleconn);


DataSet oledsexcle = new DataSet ();


Oledaexcel.fill (oledsexcle, name);


Oleconn.close ();


return oledsexcle;


}


catch (Exception err)


{


MessageBox.Show ("Data-bound Excel failed!") Reason for failure: "+ Err." message, "Hint information", MessageBoxButtons.OK, MessageBoxIcon.Information);


return null;


}


—————————————————— Insert Excel

<summary>


Writing to Excel documents


</summary>


<param name= "Path" > File name </param>


public bool Savefp2toexcel (string Path)


{


Try


{


String strconn = "Provider=Microsoft.Jet.OLEDB.4.0 +" + "Data source=" + Path + ";" + "Extended Properties=excel 8.0;";


OleDbConnection conn = new OleDbConnection (strconn);


Conn. Open ();


System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();


Cmd. Connection =conn;


for (int i=0;i<fp2. Sheets [0]. rowcount-1;i++)


{


if (FP2. Sheets [0]. cells[i,0]. Text!= "")


{


Cmd.commandtext = "INSERT into [sheet1$] (work number, name, department, title, date, time) VALUES ('" +FP2). Sheets [0]. cells[i,0]. text+ "', '" +


FP2. Sheets [0]. cells[i,1]. text+ "', '" +FP2. Sheets [0]. cells[i,2]. text+ "', '" +FP2. Sheets [0]. cells[i,3]. text+


"', '" +FP2. Sheets [0]. cells[i,4]. text+ "', '" +FP2. Sheets [0]. cells[i,5]. text+ "')";


Cmd. ExecuteNonQuery ();


}


}


Conn. Close ();


return true;


}


catch (System.Data.OleDb.OleDbException ex)


{


System.Diagnostics.Debug.WriteLine ("Error writing to Excel:" +ex.) message);


}


return false;


}

Add, modify


_excel.application app = new _excel.applicationclass ();


App. Visible = false;


_excel.workbook book = app. Workbooks.Open (Updateexcelpath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing , System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);


_excel.worksheet sheet = (_excel.worksheet) book. ActiveSheet;


for (int i = 0; i < dt. Rows.Count; i++)


{


Sheet. Cells[i + 2, 1] = dt. Rows[i][0]. ToString ();


Sheet. Cells[i + 2, 2] = dt. ROWS[I][1]. ToString ();


}


Book. Save ();


Book. Close (sheet, Updateexcelpath, System.Type.Missing);


App. Quit ();


System.GC.Collect ();

—————————————————— Modify the value of Excel
Modify the value of the first line name to John
String strcomm = "Update [sheet1$] set name= ' John ' WHERE work number = ' 132 '";
OleDbConnection myconn = new OleDbConnection (strconn);
MyConn.Open ();
OleDbCommand com = new OleDbCommand (Strcomm, myconn);
Com. ExecuteNonQuery ();
Myconn.close ();