如何使用Apache POI处理空行?(How To handle Null Row using Apache POI?)
我正在使用Apache POI来读取
xlsx
文件,它运行良好。 当找到行为null时,我有问题,我怎么能处理它? 我的文件包含500行,但它显示105667行,其余行找到null。二手代码:
import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author SAMEEK */ public class readXLSXFile { public int getNumberOfColumn(String fileName, int sheetIndex) throws FileNotFoundException, IOException { File inputFile = null; FileInputStream fis = null; XSSFWorkbook workbook = null; XSSFSheet sheet = null; XSSFRow row = null; int lastRowNum = 0; int lastCellNum = 0; // Open the workbook inputFile = new File(fileName); fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); sheet = workbook.getSheetAt(sheetIndex); lastRowNum = sheet.getLastRowNum(); for (int i = 0; i < lastRowNum; i++) { row = sheet.getRow(i); if (row != null) { if (row.getLastCellNum() > lastCellNum) { lastCellNum = row.getLastCellNum(); } } } return lastCellNum; } public int getNumberOfRow(String fileName, int sheetIndex) throws FileNotFoundException, IOException { File inputFile = null; FileInputStream fis = null; XSSFWorkbook workbook = null; XSSFSheet sheet = null; int lastRowNum = 0; // Open the workbook inputFile = new File(fileName); fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); sheet = workbook.getSheetAt(sheetIndex); lastRowNum = sheet.getLastRowNum(); return lastRowNum; } public String[] getSheetName(String fileName) throws FileNotFoundException, IOException { int totalsheet = 0; int i = 0; String[] sheetName = null; File inputFile = null; FileInputStream fis = null; XSSFWorkbook workbook = null; // Open the workbook inputFile = new File(fileName); fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); totalsheet = workbook.getNumberOfSheets(); sheetName = new String[totalsheet]; while (i < totalsheet) { sheetName[i] = workbook.getSheetName(i); i++; } return sheetName; } public int getNumberOfSheet(String fileName) throws FileNotFoundException, IOException { int totalsheet = 0; File inputFile = null; FileInputStream fis = null; XSSFWorkbook workbook = null; XSSFSheet sheet = null; int lastRowNum = 0; // Open the workbook inputFile = new File(fileName); fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); totalsheet = workbook.getNumberOfSheets(); return totalsheet; } public String[][] getSheetData(String fileName, int sheetIndex) throws FileNotFoundException, IOException, InvalidFormatException { String[][] data = null; int i = 0; int j = 0;Cell cell=null; long emptyrowcount = 0; InputStream inputStream = new FileInputStream( fileName); // Create a workbook object. Workbook wb = WorkbookFactory.create(inputStream); wb.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); Sheet sheet = wb.getSheetAt(sheetIndex); // Iterate over all the row and cells int noOfColumns = getNumberOfColumn(fileName, sheetIndex); System.out.println("noOfColumns::" + noOfColumns); int noOfRows = getNumberOfRow(fileName, sheetIndex) + 1; System.out.println("noOfRows::" + noOfRows); data = new String[noOfRows][noOfColumns]; for (int k = 0; k < noOfRows; k++) { Row row = sheet.getRow(k); if (row == null) { } else { j = 0; for (int l = 0; l < noOfColumns; l++) { // Cell cell = cit.next(); cell = row.getCell(j); if (cell.getCellType() == cell.CELL_TYPE_BLANK) { cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK); } data[i][j] = getCellValueAsString(cell); j++; } i++; } } return data; } /** * This method for the type of data in the cell, extracts the data and * returns it as a string. */ public static String getCellValueAsString(Cell cell) { String strCellValue = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: strCellValue = cell.toString(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat dateFormat = new SimpleDateFormat( "dd/MM/yyyy"); strCellValue = dateFormat.format(cell.getDateCellValue()); } else { Double value = cell.getNumericCellValue(); Long longValue = value.longValue(); strCellValue = new String(longValue.toString()); } break; case Cell.CELL_TYPE_BOOLEAN: strCellValue = new String(new Boolean( cell.getBooleanCellValue()).toString()); break; case Cell.CELL_TYPE_BLANK: strCellValue = ""; break; } } return strCellValue; } public static void main(String s[]) { try { readXLSXFile readXLSxFile = new readXLSXFile(); String[][] sheetData = readXLSxFile.getSheetData("F:/work.xlsx", 0); int columnLength = 0; columnLength = readXLSxFile.getNumberOfColumn("F:/work.xlsx", 0); int rowLength = 0; rowLength = readXLSxFile.getNumberOfRow("F:/work.xlsx", 0); int h = 0; int j = 0; while (j < rowLength) { h = 0; while (h < columnLength) { System.out.print("\t " + sheetData[j][h]); h++; } System.out.println(""); j++; } } catch (InvalidFormatException ex) { Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex); } } }
请帮我解决如何处理excel表中的空行?
I am using Apache POI to read
xlsx
file, it works well. I have question to you when row is found null, how I'm able to handle it? My file contain 500 row, but it show 105667 row, rest of row found null.used code:
import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author SAMEEK */ public class readXLSXFile { public int getNumberOfColumn(String fileName, int sheetIndex) throws FileNotFoundException, IOException { File inputFile = null; FileInputStream fis = null; XSSFWorkbook workbook = null; XSSFSheet sheet = null; XSSFRow row = null; int lastRowNum = 0; int lastCellNum = 0; // Open the workbook inputFile = new File(fileName); fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); sheet = workbook.getSheetAt(sheetIndex); lastRowNum = sheet.getLastRowNum(); for (int i = 0; i < lastRowNum; i++) { row = sheet.getRow(i); if (row != null) { if (row.getLastCellNum() > lastCellNum) { lastCellNum = row.getLastCellNum(); } } } return lastCellNum; } public int getNumberOfRow(String fileName, int sheetIndex) throws FileNotFoundException, IOException { File inputFile = null; FileInputStream fis = null; XSSFWorkbook workbook = null; XSSFSheet sheet = null; int lastRowNum = 0; // Open the workbook inputFile = new File(fileName); fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); sheet = workbook.getSheetAt(sheetIndex); lastRowNum = sheet.getLastRowNum(); return lastRowNum; } public String[] getSheetName(String fileName) throws FileNotFoundException, IOException { int totalsheet = 0; int i = 0; String[] sheetName = null; File inputFile = null; FileInputStream fis = null; XSSFWorkbook workbook = null; // Open the workbook inputFile = new File(fileName); fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); totalsheet = workbook.getNumberOfSheets(); sheetName = new String[totalsheet]; while (i < totalsheet) { sheetName[i] = workbook.getSheetName(i); i++; } return sheetName; } public int getNumberOfSheet(String fileName) throws FileNotFoundException, IOException { int totalsheet = 0; File inputFile = null; FileInputStream fis = null; XSSFWorkbook workbook = null; XSSFSheet sheet = null; int lastRowNum = 0; // Open the workbook inputFile = new File(fileName); fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); totalsheet = workbook.getNumberOfSheets(); return totalsheet; } public String[][] getSheetData(String fileName, int sheetIndex) throws FileNotFoundException, IOException, InvalidFormatException { String[][] data = null; int i = 0; int j = 0;Cell cell=null; long emptyrowcount = 0; InputStream inputStream = new FileInputStream( fileName); // Create a workbook object. Workbook wb = WorkbookFactory.create(inputStream); wb.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); Sheet sheet = wb.getSheetAt(sheetIndex); // Iterate over all the row and cells int noOfColumns = getNumberOfColumn(fileName, sheetIndex); System.out.println("noOfColumns::" + noOfColumns); int noOfRows = getNumberOfRow(fileName, sheetIndex) + 1; System.out.println("noOfRows::" + noOfRows); data = new String[noOfRows][noOfColumns]; for (int k = 0; k < noOfRows; k++) { Row row = sheet.getRow(k); if (row == null) { } else { j = 0; for (int l = 0; l < noOfColumns; l++) { // Cell cell = cit.next(); cell = row.getCell(j); if (cell.getCellType() == cell.CELL_TYPE_BLANK) { cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK); } data[i][j] = getCellValueAsString(cell); j++; } i++; } } return data; } /** * This method for the type of data in the cell, extracts the data and * returns it as a string. */ public static String getCellValueAsString(Cell cell) { String strCellValue = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: strCellValue = cell.toString(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat dateFormat = new SimpleDateFormat( "dd/MM/yyyy"); strCellValue = dateFormat.format(cell.getDateCellValue()); } else { Double value = cell.getNumericCellValue(); Long longValue = value.longValue(); strCellValue = new String(longValue.toString()); } break; case Cell.CELL_TYPE_BOOLEAN: strCellValue = new String(new Boolean( cell.getBooleanCellValue()).toString()); break; case Cell.CELL_TYPE_BLANK: strCellValue = ""; break; } } return strCellValue; } public static void main(String s[]) { try { readXLSXFile readXLSxFile = new readXLSXFile(); String[][] sheetData = readXLSxFile.getSheetData("F:/work.xlsx", 0); int columnLength = 0; columnLength = readXLSxFile.getNumberOfColumn("F:/work.xlsx", 0); int rowLength = 0; rowLength = readXLSxFile.getNumberOfRow("F:/work.xlsx", 0); int h = 0; int j = 0; while (j < rowLength) { h = 0; while (h < columnLength) { System.out.print("\t " + sheetData[j][h]); h++; } System.out.println(""); j++; } } catch (InvalidFormatException ex) { Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex); } } }
Please help me how to handle null row in excel sheet?
原文:https://stackoverflow.com/questions/9171861
满意答案
你对monad的使用(和滥用)肯定很尴尬:
- 通常通过堆叠多个变压器来逐个构建单子
- 它通常不太常见,但有时仍然会发生堆叠几个状态
- 堆叠几个Maybe变换器是非常不寻常的
- 使用MaybeT来中断循环更加不寻常
你的代码有点过于无意义了:
(`when` mzero) . isJust =<< runMaybeT (mapM_ f bases)
而不是更容易阅读
let isHappy = isJust $ runMaybeT (mapM_ f bases) when isHappy mzero
现在关注函数solve1,让我们简化它。 一个简单的方法是删除内在的MaybeT monad。 当找到一个快乐的数字时,你可以走另一条路,只有在数字不满意的情况下才能解决问题。
而且,你也不需要国家单身,是吗? 人们总是可以用显式参数替换状态。
应用这些想法solve1现在看起来好多了:
solve1 :: [Integer] -> IsHappyMemo Integer solve1 bases = go 2 where go i = do happyBases <- mapM (\b -> isHappy Set.empty b i) bases if and happyBases then return i else go (i+1)
我对这段代码更加满意。 其余的解决方案都很好。 困扰我的一件事是你丢弃了每个子问题的备忘录缓存。 这有什么理由吗?
solve :: [String] -> String solve = concat . (`evalState` Map.empty) . mapM f . zip [1 :: Integer ..] where f (idx, prob) = do s <- solve1 . map read . words $ prob return $ "Case #" ++ show idx ++ ": " ++ show s ++ "\n"
如果你重新使用它,你的解决方案不会更有效吗?
solve :: [String] -> String solve cases = (`evalState` Map.empty) $ do solutions <- mapM f (zip [1 :: Integer ..] cases) return (unlines solutions) where f (idx, prob) = do s <- solve1 . map read . words $ prob return $ "Case #" ++ show idx ++ ": " ++ show s
Your solution is certainly awkward in its use (and abuse) of monads:
- It is usual to build monads piecemeal by stacking several transformers
- It is less usual, but still happens sometimes, to stack several states
- It is very unusual to stack several Maybe transformers
- It is even more unusual to use MaybeT to interrupt a loop
Your code is a bit too pointless :
(`when` mzero) . isJust =<< runMaybeT (mapM_ f bases)
instead of the easier to read
let isHappy = isJust $ runMaybeT (mapM_ f bases) when isHappy mzero
Focusing now on function solve1, let us simplify it. An easy way to do so is to remove the inner MaybeT monad. Instead of a forever loop which breaks when a happy number is found, you can go the other way around and recurse only if the number is not happy.
Moreover, you don't really need the State monad either, do you ? One can always replace the state with an explicit argument.
Applying these ideas solve1 now looks much better:
solve1 :: [Integer] -> IsHappyMemo Integer solve1 bases = go 2 where go i = do happyBases <- mapM (\b -> isHappy Set.empty b i) bases if and happyBases then return i else go (i+1)
I would be more han happy with that code. The rest of your solution is fine. One thing that bothers me is that you throw away the memo cache for every subproblem. Is there a reason for that?
solve :: [String] -> String solve = concat . (`evalState` Map.empty) . mapM f . zip [1 :: Integer ..] where f (idx, prob) = do s <- solve1 . map read . words $ prob return $ "Case #" ++ show idx ++ ": " ++ show s ++ "\n"
Wouldn't your solution be more efficient if you reused it instead ?
solve :: [String] -> String solve cases = (`evalState` Map.empty) $ do solutions <- mapM f (zip [1 :: Integer ..] cases) return (unlines solutions) where f (idx, prob) = do s <- solve1 . map read . words $ prob return $ "Case #" ++ show idx ++ ": " ++ show s
相关问答
更多提起以修复单体变压器堆叠的*内部*(Lift to fix the *inside* of a monad transformer stack)
避免与Monad变压器电梯(Avoiding lift with monad transformers)
修改变压器堆栈中的内部读取器(Modifying inner reader in a transformer stack)
使用或滥用错误Monad变压器(Use or Abuse of the Error Monad Transformer)
Monad变压器 - 显式提升(Monad transformer – Explicit lifting)
使用哪种Monad变压器?(Which Monad Transformer to use?)
尴尬的monad变压器堆栈(awkward monad transformer stack)
在变压器堆栈的基础上插入ErrorT(Inserting ErrorT at the base of transformer stack)
Monad变压器在斯卡拉堆叠(Monad Transformer stacks in Scala)
Monad Transformer与MaybeT和RandT叠加(Monad Transformer stacks with MaybeT and RandT)
相关文章
更多Apache POI的使用
使用POI操作Excel和Word
POI设置打印区域
POI核心API
Apache POI开发环境配置
POI Fonts 字体样式处理
POI 操作Excel公式
POI 操作 Excel的主要API
Setting up Nutch 2.1 with MySQL to handle UTF-8
POI Cells单元格数据处理
最新问答
更多获取MVC 4使用的DisplayMode后缀(Get the DisplayMode Suffix being used by MVC 4)
如何通过引用返回对象?(How is returning an object by reference possible?)
矩阵如何存储在内存中?(How are matrices stored in memory?)
每个请求的Java新会话?(Java New Session For Each Request?)
css:浮动div中重叠的标题h1(css: overlapping headlines h1 in floated divs)
无论图像如何,Caffe预测同一类(Caffe predicts same class regardless of image)
xcode语法颜色编码解释?(xcode syntax color coding explained?)
在Access 2010 Runtime中使用Office 2000校对工具(Use Office 2000 proofing tools in Access 2010 Runtime)
从单独的Web主机将图像传输到服务器上(Getting images onto server from separate web host)
从旧版本复制文件并保留它们(旧/新版本)(Copy a file from old revision and keep both of them (old / new revision))
Copyright ©2023 peixunduo.com All Rights Reserved.粤ICP备14003112号
本站部分内容来源于互联网,仅供学习和参考使用,请莫用于商业用途。如有侵犯你的版权,请联系我们(neng862121861#163.com),本站将尽快处理。谢谢合作!