Answer the question
In order to leave comments, you need to log in
How to read xlsx file using POI?
I read the xls file using POI HSSF, everything was correct. But it did not learn how to read the xlsx input file. Previously, I didn’t work with xlsx, I read material on various resources on the network, I didn’t notice the difference, except for replacing HSSF with XSSF and connecting additional. libraries. However, it throws an error:
Exception in thread "main" java.lang.NoSuchMethodError: org.apache.poi.util.XMLHelper.newDocumentBuilder()Ljavax/xml/parsers/DocumentBuilder;
Swears at
XSSFWorkbook wb = new XSSFWorkbook(p_file);
def res = SSC.execute(connection,file,"test.xlsx")
The code is given below (I would be very grateful if anyone can help me solve the problem now / don't pay attention to ";" because I use groovy, the rest is all similar to java):
import groovy.sql.Sql
import org.apache.poi.xssf.usermodel.XSSFCell
import javax.xml.soap.Text
import java.sql.Blob
import java.sql.Connection
import java.sql.DriverManager
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.XSSFWorkbook; //New imports to read XLSX format
import org.apache.poi.xssf.usermodel.XSSFSheet; //New imports to read XLSX format
import org.apache.poi.ss.usermodel.*;
import java.util.Iterator;
import java.io.*;
class XM_PARSE_XLS {
def execute(Connection conn, InputStream p_file, String p_filename) {
Sql sql = new Sql(conn)
XSSFWorkbook wb = new XSSFWorkbook(p_file);
ArrayList<HashMap<String, String>> arrAllData = new ArrayList<HashMap<String, String>>();
String strsql
Integer cntStr = 0
HashSet rw_okpo = new HashSet();
// идем по листам в файле
wb.each { XSSFSheet myExcelSheet ->
DataFormatter formatter = new DataFormatter(Locale.ROOT);
// идем по страницам файла
myExcelSheet.each{ Row myrow ->
HashMap<String, String> hm = new HashMap<String, String>();
if (cntStr >= 0) {
// идем по строкам с данными
Integer numCell = 0;
String typ = '';
myrow.each { Cell mycell ->
String value = ""; // приводит любые ячейки к строковому формату
// если тип Строка
if (mycell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
value = mycell.getStringCellValue();
typ = 'S'
// если тип число
} else {
// а для числовых ячеек или ячеек даты значение будет отформатировано на основе правил форматирования / стиля, примененных к ячейке, а затем возвращено как строка
value = formatter.formatCellValue(mycell);
value = value.replace(',','');
typ = 'N'
if (cntStr >= 0){
switch(numCell) { //0-17
case 0:
hm.put("t0", value + ";");
break;
case 1:
hm.put("t1", value + ";");
break;
case 2:
hm.put("t2", value + ";");
break;
case 3:
hm.put("t3", value + ";");
break;
case 4:
hm.put("t4", value + ";");
break;
case 5:
hm.put("t5", value + ";");
break;
case 6:
hm.put("t6", value + ";");
break;
case 7:
hm.put("t7", value + ";");
break;
case 8:
hm.put("t8", value + ";");
break;
case 9:
hm.put("t9", value + ";");
break;
case 10:
hm.put("t10", value + ";");
break;
case 11:
hm.put("t11", value + ";");
break;
case 12:
hm.put("t12", value + ";");
break;
case 13:
hm.put("t13", value + ";");
break;
case 14:
hm.put("t14", value + ";");
break;
case 15:
hm.put("t15", value + ";");
break;
case 16:
hm.put("t16", value + ";");
break;
case 17:
hm.put("t17", value + "\r\n");
break;
}
numCell = numCell + 1;
}
}
}
if(hm) arrAllData.add(hm);
cntStr = cntStr + 1;
}
}
return res;
}
static void main(String... args) {
Class.forName("oracle.jdbc.driver.OracleDriver")
//Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@10.193.21.75:3333/OLAP2", "LAS", "ASfDER7F4FA")
connection.setAutoCommit(true)
try {
def file = new File("test.xlsx").newInputStream()
def SSC = new XM_PARSE_XLS()
def res = SSC.execute(connection,file,"test.xlsx")
} finally {
connection.close()
}
}
}
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question