java遍历Excel行

121 阅读 0 评论 80 点赞

我是靠谱客的博主无辜毛衣，这篇文章主要介绍java遍历Excel行，现在分享给大家，希望可以做个参考。

背景：从数据库导出数据到Excel文档，再从Excel中截取需要的数据列形成sql，去操作sql。

废话少说，直接贴代码

复制代码public static void test() throws Exception { InputStream is = new FileInputStream("C:\Users\Administrator\Desktop\update.xlsx"); Workbook wb = WorkbookFactory.create(is); is.close(); Sheet sheet = wb.getSheetAt(0); int trLength = sheet.getLastRowNum(); log.info("最大行号：{}", trLength); StringBuilder sql = new StringBuilder(); for (int i = 1; i <= trLength; i++) { Row row = sheet.getRow(i); String cardLength = ""; //修正报错：Cannot get a text value from a numeric cell if(row.getCell(8)!=null){ row.getCell(8).setCellType(Cell.CELL_TYPE_STRING); cardLength = row.getCell(8).getStringCellValue(); } String bankBinCode = ""; if(row.getCell(13)!=null){ row.getCell(13).setCellType(Cell.CELL_TYPE_STRING); bankBinCode = row.getCell(13).getStringCellValue(); } sql.append("=======此处拼装SQL========="); } log.info(sql.toString()); FileOutputStream fos = new FileOutputStream(new File("C:\Users\Administrator\Desktop\update-sql.txt")); byte b[] = sql.toString().getBytes(); fos.write(b); fos.close(); }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
public static void test() throws Exception {
InputStream is = new FileInputStream("C:\Users\Administrator\Desktop\update.xlsx");
Workbook wb = WorkbookFactory.create(is);
is.close();
Sheet sheet = wb.getSheetAt(0);
int trLength = sheet.getLastRowNum();
log.info("最大行号：{}", trLength);
StringBuilder sql = new StringBuilder();
for (int i = 1; i <= trLength; i++) {
Row row = sheet.getRow(i);
String cardLength = "";
//修正报错：Cannot get a text value from a numeric cell
if(row.getCell(8)!=null){
row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
cardLength = row.getCell(8).getStringCellValue();
}
String bankBinCode = "";
if(row.getCell(13)!=null){
row.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
bankBinCode = row.getCell(13).getStringCellValue();
}
sql.append("=======此处拼装SQL=========");
}
log.info(sql.toString());
FileOutputStream fos = new FileOutputStream(new File("C:\Users\Administrator\Desktop\update-sql.txt"));
byte b[] = sql.toString().getBytes();
fos.write(b);
fos.close();
}

引用jar包：

poi-3.14.jar
poi-ooxml-3.14.jar
poi-ooxml-schemas-3.14.jar
xmlbeans-2.6.0.jar

遇到的问题：
1、java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject
      是由缺失xmlbeans-2.6.0.jar包导致。
2、Cannot get a text value from a numeric cell
      需要先设置cell为String类型：
if(row.getCell(8)!=null){
               row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
               cardLength = row.getCell(8).getStringCellValue();
           }

感谢csdn同行们的帮助：
http://blog.csdn.net/liucheng417/article/details/52727018
http://blog.csdn.net/ysughw/article/details/9288307