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

Source: Internet
Author: User
Tags int size
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/

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.