Answer the question
In order to leave comments, you need to log in
Why does groovy (java) replace it with multiple numbers separated by commas when reading from excel a single number separated by spaces?
Hi all.
There is an Excel file that contains a digit group separator (the number 8197023.10 looks like 8 197 023.10), but when read in groovy (java) it turns out the number 8,197,023.10
What is the reason for this and how can I fix it? The code is below, summa.
import groovy.sql.Sql
import java.sql.Connection
import java.sql.DriverManager
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.usermodel.*
class XM_PARSE_XLS {
def execute(Connection conn, InputStream p_file, String p_filename) {
Sql sql = new Sql(conn);
HSSFWorkbook wb = new HSSFWorkbook(p_file);
ArrayList<HashMap<String, String>> arrAllData = new ArrayList<HashMap<String, String>>();
String strsql
Integer cntStr = 0
String els_list
String st_code_list
String rep_date_list
// идем по листам в файле
wb.each { HSSFSheet myExcelSheet ->
DataFormatter formatter = new DataFormatter(Locale.US);
// идем по страницам файла
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() == HSSFCell.CELL_TYPE_STRING) {
value = mycell.getStringCellValue();
typ = 'S'
// если тип число
} else {
// а для числовых ячеек или ячеек даты значение будет отформатировано на основе правил форматирования / стиля, примененных к ячейке, а затем возвращено как строка
value = formatter.formatCellValue(mycell);
typ = 'N'
}
if (cntStr == 0) {
Boolean Err = false;
switch(numCell) {
case 0:
Err=(value.toUpperCase()!='ELS');
break;
case 1:
Err=(value.toUpperCase()!='ST_CODE');
break;
case 2:
Err=(value.toUpperCase()!='SUMMA');
break;
case 3:
Err=(value.toUpperCase()!='REP_DATE');
break;
}
numCell = numCell + 1;
if (Err) {
throw new Exception("Неверный заголовок столбца "+ value)
}
} else {
if( (typ!='N') && (numCell>0)&&(numCell<3) ){
throw new Exception("Неверные данные для загрузки "+ value)
}
switch(numCell) {
case 0:
hm.put("els", value);
break;
case 1:
hm.put("st_code", value);
break;
case 2:
hm.put("summa", value = 0.00);
break;
case 3:
hm.put("rep_date", value);
break;
}
numCell = numCell + 1;
}
}
}
if(hm) arrAllData.add(hm);
cntStr = cntStr + 1;
}
}
for (int i = 1; i < arrAllData.size(); i++) {
HashMap <String, String> hm = arrAllData.get(i);
if (els_list == null) {
els_list = hm?.els}
else{
els_list = els_list+";"+hm?.els}
if (st_code_list == null) {
st_code_list = hm?.st_code}
else{
st_code_list = st_code_list+";"+hm?.st_code}
if (rep_date_list == null) {
rep_date_list = hm?.rep_date}
else{
rep_date_list = rep_date_list+";"+hm?.rep_date}
strsql = """plsql code
""";
sql.execute( strsql);
}
HashMap<String, Object> res = new HashMap<>();
res.put("P_CNT",cntStr-1);
res.put("P_SQL",strsql);
res.put("P_ELS_LIST",els_list);
res.put("P_ST_CODE_LIST",st_code_list);
res.put("P_REP_DATE_LIST",rep_date_list);
return res;
}
static void main(String... args) {
Class.forName("oracle.jdbc.driver.OracleDriver")
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@ip:port/OLAP2", "name", "password")
connection.setAutoCommit(true)
try {
def file = new File("name.xls").newInputStream()
def SSC = new XM_PARSE_XLS()
def res = SSC.execute(connection,file,"name.xls")
} 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