NPOI 操作Excel

1. 读Excel ，将结果封装成一个dataTable

 

 public static DataTable readExcel(string filePath) {

            DataTable dt = new DataTable();

            HSSFWorkbook workbook = null;

            FileStream fs = null ;

            try

            {

                fs = new FileStream(filePath, FileMode.Open,FileAccess.Read);

                workbook = new HSSFWorkbook(fs);

               

                HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);

                //获取Excle的行信息

                HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;

               //获取一行有多少列

                int cellsCount = headerRow.LastCellNum;

              

                for (int i = 0; i < cellsCount;i++ )

                {

                    HSSFCell headerCell = headerRow.GetCell(i) as HSSFCell;

 

                    dt.Columns.Add(headerCell.ToString());

                }

 

               //读取Excel具体的信息

 

                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum;i++ )

                {

                    HSSFRow row = sheet.GetRow(i) as HSSFRow;

                    DataRow dtRow = dt.NewRow();

                    for (int j = row.FirstCellNum; j < row.LastCellNum;j++ )

                    {

                        HSSFCell cell = row.GetCell(j) as HSSFCell;

                        if (cell.CellType == CellType.Numeric)

                        {

                            dtRow[j] = cell.NumericCellValue;

                        }

                        else {

                            dtRow[j] = cell.StringCellValue;

                        }

                     

                       

                        Console.Write(cell.StringCellValue+"/");

                      

                    }

                    dt.Rows.Add(dtRow);

                    Console.WriteLine();

                }

 

            }

            catch(Exception ex) {

                ex.StackTrace.ToString();

            }

            return dt;

        }

 

 

 

 

2  //生成一个新的Excel

 

 

1.  从数据库中查询数据封装成Datatable

 

 

 

        public static DataTable getData()

        {

            string gfitConnStr = "Uid=xxx;Pwd=xxx;Data Source=142.100.16.120:1521/GFIT";

            DataTable dt = new DataTable();

            OracleConnection gfitConn = null;

            string selectSql = "select USER_NAME,PASSWORD from apps.sys_users";

            try {

                gfitConn = new OracleConnection(gfitConnStr);

                gfitConn.Open();

                OracleDataAdapter da = new OracleDataAdapter(selectSql, gfitConn);

                da.Fill(dt);

            }catch(Exception ex){

           

            }

            return dt;

        }

 

 

2. 生成Excel

 

 

 

        public static void writeToExcel(string outputPath,DataTable headerTable,DataTable dt)

        {

            FileStream fs = null;

            HSSFWorkbook workbook = null;

            try {

                fs = new FileStream(outputPath, FileMode.OpenOrCreate);

                workbook = new HSSFWorkbook();

                HSSFSheet sheet = workbook.CreateSheet("userSheet") as HSSFSheet;

 

                //生成Excel头信息

 

                HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;

                for (int i = 0; i < headerTable.Columns.Count; i++)

                {

                    HSSFCell headerCell = headerRow.CreateCell(i) as HSSFCell;

                    headerCell.SetCellValue(headerTable.Rows[0][i].ToString());

                }

              

                //生成内容

                for (int i = 1; i < dt.Rows.Count;i++ )

                {

                    HSSFRow lineRow = sheet.CreateRow(i) as HSSFRow;

                    for (int j = 0; j < dt.Columns.Count;j++ )

                    {

                      HSSFCell lineCell =  lineRow.CreateCell(j) as HSSFCell;

                      lineCell.SetCellValue(dt.Rows[i][j].ToString());

                    }

                }

 

                workbook.Write(fs);

                fs.Flush();

              

            }catch(Exception ex){

           

            }

        }

 

 

3. 调用

 

 

  static void Main(string[] args)

        {

          

       

            try

            {

                //读Excel

               // string excelPath = "D:\\data\\PS_barcode.xls";

               // ExcelOp.readExcel(excelPath);

 

 

                //向Excel中写数据

      

                DataTable headerTable = new DataTable();

                headerTable.Columns.Add("userName", Type.GetType("System.String"));

                headerTable.Columns.Add("userPass", Type.GetType("System.String"));

                DataRow headerRow = headerTable.NewRow();

                headerRow["userName"] = "用户名";

                headerRow["userPass"] = "密码";

                headerTable.Rows.Add(headerRow);

               

 

                DataTable lineTable = getData();

                string outputPath = "D:\\data\\final\\1.xls";

                Console.WriteLine("开始导出");

                ExcelOp.writeToExcel(outputPath, headerTable, lineTable);

                Console.WriteLine("导出结束...");

 

            }

            catch (Exception ex)

            {

                ex.StackTrace.ToString();

 

            }

            finally {

           

            }

 

        }