我想在第三行,插入数据库的数据,这里假如数据库有10条,并且继承第二行的格式
数据库数据
{"clark",25},我写个json对象,10条这个
造数据代码
JSONArray jsonArray = new JSONArray();for (int i = 0; i < 10; i++) {JSONObject jsonObject = new JSONObject();jsonObject.put("name","clark");jsonObject.put("age",i+10);jsonArray.add(jsonObject);}
poi依赖
读xls,xlsx,io流,fastJson
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.2</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.2</version> </dependency><dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>1.4</version>
</dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.83</version> </dependency>
模板文件(测试.xlsx)
代码逻辑,读模板,插行,同时继承第二行样式,填数据
整体,可直接复制
public static void main(String[] args) {JSONArray jsonArray = new JSONArray();for (int i = 0; i < 10; i++) {JSONObject jsonObject = new JSONObject();jsonObject.put("name","clark");jsonObject.put("age",i+10);jsonArray.add(jsonObject);}//读取模板try {FileInputStream inputStream =new FileInputStream("C:\\Users\\shiao\\Desktop\\测试.xlsx");XSSFWorkbook workbook = new XSSFWorkbook(inputStream);XSSFSheet sheet = workbook.getSheetAt(0);//获取表中的第一个sheetXSSFRow row = sheet.getRow(1);XSSFCell cell = row.getCell(0);//姓名的样式XSSFCellStyle nameStyle = cell.getCellStyle();System.out.println(cell.getStringCellValue());//字符串用string//年龄的样式cell= row.getCell(1);XSSFCellStyle ageStyle = cell.getCellStyle();System.out.println(cell.getRawValue());//数字用rawValue//获取后进行遍历数据库for (int i = 0; i < jsonArray.size(); i++) {JSONObject jsonObject = jsonArray.getJSONObject(i);//新增行XSSFRow rowNew = sheet.createRow(3+i);//参数代表从第几行新增//给新增行增加样式XSSFCell cellName = rowNew.createCell(0);//第1列,代表name的位置XSSFCell cellAge = rowNew.createCell(1);//第2列,代表age的位置cellName.setCellStyle(nameStyle);cellAge.setCellStyle(ageStyle);cellName.setCellValue(jsonObject.getString("name"));cellAge.setCellValue(jsonObject.getInteger("age"));}workbook.write(new FileOutputStream("C:\\Users\\shiao\\Desktop\\测试结果.xlsx"));} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}
}
结果
但是很明显看到,之前的第五行被覆盖了,如果不让他覆盖怎么办,那就让插入位置的,到最后的其他整体下移动一行
//先全部下移动1行,这样才能保证不覆盖 // (参数1是开始,参数2是结束,参数3是正数代表,开始-结束范围内的单元格下移动1行) sheet.shiftRows(3+i,sheet.getLastRowNum(),1);
结果