EXCEL导入导出

以下演示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倍。