c# 讀取和寫入excel資料

標籤：

1. 讀取

DataTable GetDataFromExcelByConn(bool hasTitle = false)
{
    OpenFileDialog openFile = new OpenFileDialog();
    openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
    openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
    openFile.Multiselect = false;
    if (openFile.ShowDialog() == DialogResult.Cancel) return null;
    var filePath = openFile.FileName;
    string fileType = System.IO.Path.GetExtension(filePath);
    if (string.IsNullOrEmpty(fileType)) return null;

    using (DataSet ds = new DataSet())
    {
        string strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" +
                        "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
                        "data source={3};",
                        (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), filePath);
        string strCom = " SELECT * FROM [Sheet1$]";
        using (OleDbConnection myConn = new OleDbConnection(strCon))
        using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn))
        {
            myConn.Open();
            myCommand.Fill(ds);
        }
        if (ds == null || ds.Tables.Count <= 0) return null;
        return ds.Tables[0];
    }
}

 

2. 寫入

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();
 

c# 讀取和寫入excel資料