EXCEL匯入匯出

標籤：des   style   blog   http   color   io   os   使用   ar   

以下示範Excel檔案匯入Sql server和Sql server資料寫入Excel檔案。

Excel檔案匯入Sql server:1.先把Excel檔案資料讀取到DataTable(使用NOPI)

                                  2.把DataTable插入sql server(使用SqlBulkCopy)

 

       //做法:讀取EXCEL資料到DataTable，把DataTable插入資料庫            String filename="";            OpenFileDialog openfile = new OpenFileDialog();            openfile.Filter = "EXCEL檔案|*.xls|EXCEL檔案|*xlsx";            openfile.FilterIndex = 0;            openfile.Title = "選擇檔案";            if (openfile.ShowDialog() != true)            {                return;            }            filename = openfile.FileName;            DateTime startimport = DateTime.Now;//開始            using (FileStream filestream = new FileStream(filename, FileMode.Open))            {                HSSFWorkbook workbook = new HSSFWorkbook(filestream);                DataTable dt = new DataTable();                ISheet sheet= workbook.GetSheetAt(0);//擷取Excel中第一個表                IRow headrow = sheet.GetRow(0);                int cellcount = headrow.Cells.Count;                 //int cellcount = headrow.LastCellNum;                int rowcount = sheet.LastRowNum;                //建立標題列                for (int i = headrow.FirstCellNum; i < cellcount; i++)                {                    DataColumn datacolumn = new DataColumn(headrow.GetCell(i).StringCellValue);                    dt.Columns.Add(datacolumn);                }                //寫入資料                for (int i = (sheet.FirstRowNum + 1); i < rowcount+1; i++)                {                    IRow row=sheet.GetRow(i);                    DataRow datarow = dt.NewRow();                    for (int j = row.FirstCellNum; j < cellcount; j++)                    {                        if (j == row.FirstCellNum)                        {                            datarow[j] = Convert.ToInt64(row.GetCell(j).StringCellValue);                        }                        else                        {                            datarow[j]=row.GetCell(j).StringCellValue;                        }                    }                    dt.Rows.Add(datarow);                }                //把DataTable寫入sql server                String connectionstring = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;                SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionstring);                sqlbulkcopy.DestinationTableName="T_TelNumber2";                sqlbulkcopy.WriteToServer(dt);                DateTime endimport = DateTime.Now;                MessageBox.Show((endimport - startimport).ToString(), "所用時間", MessageBoxButton.OKCancel, MessageBoxImage.Exclamation);            }            

匯入6W多條資料只要3.3秒

Sql server資料寫入Excel檔案:1.讀取Sql server表中的資料到DataTable(填充資料集)

                                        2.把DataTable寫入Excel(NPOI)

  //做法:讀取Sql server表中的資料到DataTable，把DataTable寫入Excel            DateTime startexport = DateTime.Now;//開始            DataTable dt;//暫存匯出資料的資料表            String connectionstring = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;            using (SqlConnection conn = new SqlConnection(connectionstring))            {                using (SqlCommand cmd = conn.CreateCommand())                {                    cmd.CommandText = "select * from T_TelNumber";                    DataSet ds = new DataSet();                    SqlDataAdapter adapter = new SqlDataAdapter();                    adapter.SelectCommand = cmd;                    adapter.Fill(ds);                    dt = ds.Tables[0];                }            }                        //使用NPOI將dt寫入Excel            //使用NPOI前先引用程式集(NPOI,NPOI.OOXML)，再引用名稱空間(NPOI.HSSF.UserModel,NPOI.XSSF.UserModel,NPOI.SS.UserModel)            HSSFWorkbook workbook = new HSSFWorkbook();            ISheet sheet=workbook.CreateSheet("號碼歸屬地");            IRow headrow = sheet.CreateRow(0);//編寫標題列            headrow.CreateCell(0, CellType.NUMERIC).SetCellValue("ID");            headrow.CreateCell(1, CellType.STRING).SetCellValue("StartTelNumber");            headrow.CreateCell(2, CellType.STRING).SetCellValue("TelType");            headrow.CreateCell(3, CellType.STRING).SetCellValue("TelArea");                       for (int i = 0; i < 65535; i++)//excel2003最大隻能儲存65536行            {                IRow row = sheet.CreateRow(i+1);                row.CreateCell(0, CellType.NUMERIC).SetCellValue(dt.Rows[i]["id"].ToString());                row.CreateCell(1, CellType.STRING).SetCellValue(dt.Rows[i]["StartTelNumber"].ToString());                row.CreateCell(2, CellType.STRING).SetCellValue(dt.Rows[i]["TelType"].ToString());                row.CreateCell(3, CellType.STRING).SetCellValue(dt.Rows[i]["TelArea"].ToString());            }                       FileStream filestream = new FileStream("TelNumber.xls",FileMode.Create);            workbook.Write(filestream);            DateTime endexport=DateTime.Now;            MessageBox.Show((endexport-startexport).ToString(),"所用時間",MessageBoxButton.OKCancel,MessageBoxImage.Exclamation);                    }

匯出65535條資料所用時間：

 

疑惑：

NPOI中使用HSSFWorkbook最大隻能建立65536行資料(Excel 2003格式)，使用XSSFWorkbook最大可以1048576行資料(Excel 2007以上格式)

但不知道為什麼在使用XSSFWorkbook建立行並寫入資料時變得非常慢，假設同樣建立65536行資料XSSFWorkbook比HSSFWorkbook慢N倍。

 

EXCEL匯入匯出