martin_tang
2016-08-10 09:37
采纳率: 62.5%
浏览 1.6k

使用NPOI添加excel內容失敗(在現有excel檔案后添加數據失敗)

通過click事件,在檔案中新建d:\a1.xlsx這個文件,也可以保存數據;但是重新打開,添加數據失敗!
下面使用了兩種NPOI方式,全部失敗!!沒有報錯,也沒發現問題所在。
執行后都是只能顯示如下內容,也就是說可以新建並且存入,但是,但是,但是在現有excel后添加數據失敗,請大家幫忙看看,指教!
图片说明

 private void button51_Click(object sender, EventArgs e)
        {

            NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_01");

            if (rowIndex == 1)       //執行一次
            {
                NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("序號");
                headerRow.CreateCell(1).SetCellValue("測試一");
                headerRow.CreateCell(2).SetCellValue("測試二");
                headerRow.CreateCell(3).SetCellValue("測試三");
                headerRow.CreateCell(4).SetCellValue("測試四");

                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                dataRow.CreateCell(0).SetCellValue(rowIndex.ToString());
                dataRow.CreateCell(1).SetCellValue(userControl_station_status6.textbox94_int.ToString());
                dataRow.CreateCell(2).SetCellValue(userControl_station_status8.textbox93_int.ToString());
                dataRow.CreateCell(3).SetCellValue(userControl_station_status10.textbox92_int.ToString());
                dataRow.CreateCell(4).SetCellValue(userControl_station_status12.textbox91_int.ToString());
                fs = new FileStream(@"d:\a1.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);

                book.Write(fs);
                //fs.Flush();
                headerRow = null;
                dataRow = null;
                sheet = null;
                book.Close();
                book = null;
                book = null;
                book = null;
                fs.Close();
                fs.Dispose();
                 fs = null;
            }
            else
            {
                FileStream fs = new FileStream(@"d:\a1.xlsx", FileMode.Open, FileAccess.ReadWrite);
                XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
                ISheet sheet2 = workbook.GetSheetAt(0);//获取工作表
                IRow row = sheet2.GetRow(0); //得到表头
                row = sheet2.CreateRow((sheet2.LastRowNum + 1));//在工作表中添加一行

                row.CreateCell(0).SetCellValue("ftfyfghghvgh");
                row.CreateCell(1).SetCellValue("ftfyfghghvgh");             
                workbook.Write(fs);//写入文件
                workbook.Close();
                workbook = null;
                fs.Close();
                fs.Dispose();
                fs = null;
            }
       }









 private void button51_Click(object sender, EventArgs e)
        {

            NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_01");

            if (rowIndex == 1)       //執行一次
            {
                NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("序號");
                headerRow.CreateCell(1).SetCellValue("測試一");
                headerRow.CreateCell(2).SetCellValue("測試二");
                headerRow.CreateCell(3).SetCellValue("測試三");
                headerRow.CreateCell(4).SetCellValue("測試四");

                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                dataRow.CreateCell(0).SetCellValue(rowIndex.ToString());
                dataRow.CreateCell(1).SetCellValue(userControl_station_status6.textbox94_int.ToString());
                dataRow.CreateCell(2).SetCellValue(userControl_station_status8.textbox93_int.ToString());
                dataRow.CreateCell(3).SetCellValue(userControl_station_status10.textbox92_int.ToString());
                dataRow.CreateCell(4).SetCellValue(userControl_station_status12.textbox91_int.ToString());
                fs = new FileStream(@"d:\a1.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);

                book.Write(fs);
                //fs.Flush();
                headerRow = null;
                dataRow = null;
                sheet = null;
                book.Close();
                book = null;
                book = null;
                book = null;
                fs.Close();
                fs.Dispose();
                 fs = null;
            }
            else
            {



                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                dataRow.CreateCell(0).SetCellValue(rowIndex.ToString());
                dataRow.CreateCell(1).SetCellValue(userControl_station_status6.textbox94_int.ToString());
                dataRow.CreateCell(2).SetCellValue(userControl_station_status8.textbox93_int.ToString());
                dataRow.CreateCell(3).SetCellValue(userControl_station_status10.textbox92_int.ToString());
                dataRow.CreateCell(4).SetCellValue(userControl_station_status12.textbox91_int.ToString());
                fs = new FileStream(@"d:\a1.xlsx", FileMode.Append);//写入流
                book.Write(fs);
                dataRow = null;
                sheet = null;
                book.Close();
                book = null;
                book = null;
                book = null;
                fs.Close();
                fs.Dispose();
                fs = null;
            }


            rowIndex++;


        }
    }

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

5条回答 默认 最新

  • martin_tang 2016-08-12 07:52
    已采纳
       //  方法 1
                    FileStream fs = new FileStream(@"d:\a1.xlsx", FileMode.Open, FileAccess.ReadWrite);
                  //  XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
                    XSSFWorkbook  workbook = new XSSFWorkbook(fs);
                    ISheet sheet2 = workbook.GetSheetAt(0);//获取工作表
                    IRow row = sheet2.GetRow(0); //得到表头
                    row = sheet2.CreateRow((sheet2.LastRowNum + 1));//在工作表中添加一行
    
                    row.CreateCell(0).SetCellValue((sheet2.LastRowNum).ToString());
                    row.CreateCell(1).SetCellValue(userControl_station_status6.textbox94_int.ToString());
                    row.CreateCell(2).SetCellValue(userControl_station_status6.textbox93_int.ToString());
                    row.CreateCell(3).SetCellValue(userControl_station_status6.textbox92_int.ToString());
                    row.CreateCell(4).SetCellValue(userControl_station_status6.textbox91_int.ToString());
    
                    FileStream fs2 = new FileStream(@"d:\a1.xlsx", FileMode.Create, FileAccess.Write);
                    workbook.Write(fs2);
    
                  //  workbook.Write(fs);//写入文件
                    workbook.Close();
                    workbook = null;
                    fs.Close();
                    fs.Dispose();
                    fs = null;
    
                    fs2.Close();
                    fs2.Dispose();
                    fs2 = null;
    
    

    使用兩次“流”就能實現。分數給自己吧!

    打赏 评论
  • martin_tang 2016-08-10 09:52

    上面有一些錯別字,複述下
    通過click事件,在檔案中新建d:\a1.xlsx這個文件(成功), 也可以通過FileStream 保存數據(成功);
    重新打開d:\a1.xlsx,並且在後面添加一行新的數據(失敗)~~試了兩種方式都不行啊

    打赏 评论
  • devmiao 2016-08-10 09:53
    打赏 评论
  • martin_tang 2016-08-11 00:41

    有人知道嗎??
    頭痛啊!

    打赏 评论
  • _1_1_7_ 2016-08-11 01:12

    在現有excel后添加數據失敗?是不是rowIndex没有更新,又从1开始了?你需要先获取当前文档中有多数行

    打赏 评论

相关推荐 更多相似问题