Java更新excel表格数据
需求背景:我需要把excel表格数据转换 “202310”->“2023Q3”
前提:excel中数据只有一列,纯数据,可按需更改调试代码
public class PojoGenerator {
@Test
public void generateTalentDB() throws Exception{
File f = new File("C:\\Users\\uf202996\\Desktop\\无标题.xls");
InputStream inputStream = new FileInputStream(f);
HSSFWorkbook xssfWorkbook = new HSSFWorkbook(inputStream);
HSSFSheet sheet1 = xssfWorkbook.getSheetAt(0);
for(Row row : sheet1){
for(Cell hssfCell : row){
String stringCellValue = hssfCell.getStringCellValue();
String year = stringCellValue.substring(0, 4);
int month = Integer.parseInt(stringCellValue.substring(4, 6));
String quarter = getQuarter(month);
String formattedDate = year + "Q" + quarter;
hssfCell.setCellValue(formattedDate);
}
}
FileOutputStream out = new FileOutputStream(f);
xssfWorkbook.write(out);
out.close();
}
public static String getQuarter(int month) {
if (month <= 3 && month > 0) {
return "1";
} else if (month <= 6 && month > 3) {
return "2";
} else if (month <= 9 && month > 6) {
return "3";
} else if (month <= 12 && month > 9) {
return "4";
} else {
throw new IllegalArgumentException("Invalid month");
}
}