C # manipulate Excel files (read Excel, write Excel)

C # manipulate Excel files (read Excel, write Excel)2009-01-09 10:25 157690 People read Comments (59) Favorites Report Excel C # DataSet exception string properties

See the Forum there are people asking questions about reading Excel and importing Excel. Spare time will I know the operation of Excel to sum up, now share everyone, hope to everyone to bring some help.
In addition, we have to pay attention to some simple problems 1.excel files can only store 65535 rows of data, if your data is greater than 65535 rows, then you need to partition Excel. 2. About garbled, this is mainly the problem of character setting.

1. Load Excel (read Excel content) return value is a dataset[CSharp] View Plain copy print? Load Excel   public static DataSet Loaddatafromexcel (string filePath)   {      try  & nbsp;   {          string strconn;           strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + FilePath + "; Extended properties= ' Excel 8.0; Hdr=false;imex=1 ' ";          oledbconnection oleconn = new OleDbConnection ( strconn);          oleconn.open ();           String sql = "SELECT * from  [sheet1$]";/But change sheet name, such as Sheet2, etc.        & nbsp;    OleDbDataAdapter oledaexcel = new OleDbDataAdapter (sql, oleconn);           DataSet oledsexcle = new DataSet ();          Oledaexcel.fill (oledsexcle, "Sheet1");          oleconn.close ();          return oledsexcle;     }      catch (Exception err)       {          MessageBox.Show ("Data-bound Excel failed!) Reason:" + err. message, "hint info",              MessageBoxButtons.OK, MessageBoxIcon.Information);          return null;      } } 

        Load Excel public static DataSet Loaddatafromexcel (string filePath) {try
                {string strconn; strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + FilePath + "; Extended properties= ' Excel 8.0;
                Hdr=false;imex=1 ' ";
                OleDbConnection oleconn = new OleDbConnection (strconn);
                Oleconn.open (); String sql = "SELECT * FROM [sheet1$]";/But change sheet name, such as Sheet2, etc. oledbdataadapter oledaexcel = new OleD
                Bdataadapter (SQL, oleconn);
                DataSet oledsexcle = new DataSet ();
                Oledaexcel.fill (oledsexcle, "Sheet1");
                Oleconn.close ();
            return oledsexcle; The catch (Exception err) {MessageBox.Show ("Data bound Excel failed!) Reason:" + err.
                message, "Hint information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            return null;
        }} 

2. Write Excel content, parameters: exceltable is a table to be imported into Excel[CSharp] View Plain copy print? public static bool Savedatatabletoexcel (System.Data.DataTable exceltable, string filePath)            {              Microsoft.Office.Interop.Excel.Application app =                   new Microsoft.Office.Interop.Excel.ApplicationClass ();               try               {                   app. Visible = false;                  Workbook Wbook = App. Workbooks.Add (true);                   Worksheet Wsheet = wbook.worksheets[1] as worksheet;&nbsp                 if ( ExcelTable.Rows.Count > 0)                    {                       int row = 0;                       row = exceltable.rows.count;                       int col = exceltable.columns.count;                       for (int i = 0; i < row; i++)          & nbsp;            {                           for (int j = 0; J < Col J + +)                            {                               string str = exceltable.rows[i][j]. ToString ();                               Wsheet.cells[i + 2, j + 1] = str;                           }                      }  &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&Nbsp;         }                     int size = exceltable.columns.count;                   for (int i = 0; i < size; i++)   & nbsp;               {                       Wsheet.cells[1, 1 + i] = exceltable.columns[i]. columnname;                 }                  // Set up a pop-up save and overwrite query prompt box                    App. DisplayAlerts = false;                  app. alertbeforeoverwriting = false;                  //Save Workbook                    wbook.save ();                  //Save Excel file                    app. Save (FilePath);                   App. SaveWorkspace (FilePath);                   app. Quit ();                  app = null;                  return true;             }               catch (Exception err)                {                  MessageBox.Show ("Export Excel error.") Error reason: "+ err." Message, "hint",                       MessageBoxButtons.OK, messageboxicon.information);                   return false;              }               finally              {              }         } 

public static bool Savedatatabletoexcel (System.Data.DataTable exceltable, String filePath) {Microsoft .
            Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass (); try {app.
                Visible = false; Workbook Wbook = App.
                Workbooks.Add (TRUE);
                Worksheet Wsheet = wbook.worksheets[1] as worksheet;
                    if (ExcelTable.Rows.Count > 0) {int row = 0;
                    row = ExcelTable.Rows.Count;
                    int col = ExcelTable.Columns.Count;
                        for (int i = 0; i < row; i++) {for (int j = 0; J < Col; J +) {string str = Exceltable.rows[i][j].
                            ToString ();
                        Wsheet.cells[i + 2, j + 1] = str;

     }
                    }
                }           int size = ExcelTable.Columns.Count; for (int i = 0; i < size; i++) {wsheet.cells[1, 1 + i] = exceltable.columns[i].
                ColumnName; }//Set the app to prevent pop-up save and overwrite query prompt box.
                DisplayAlerts = false; App.
                Alertbeforeoverwriting = false;
                Save Workbook Wbook.save (); Save the Excel file app.
                Save (FilePath); App.
                SaveWorkspace (FilePath); App.
                Quit ();
                App = null;
            return true; The catch (Exception err) {MessageBox.Show () error exporting Excel. Error reason: "+ err."
                message, "Hint information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            return false;
 } finally {}}

Reprint friend Please indicate the source thank you. http://blog.csdn.net/gisfarmer/