Report output is a content that is often involved in Java application development, and general reports often lack universality and are not convenient for users to edit personalized. Due to its cross-platform features, Java programs cannot directly manipulate Excel. Therefore, this article discusses the POI line-of-sight Java program for Excel reading and importing.
Project structure:
java_poi_excel
Excel files used:
xls
XlsMain .java class
//This class has a main method, which is mainly responsible for running the program. At the same time, this class also includes reading Excel with poi (version 2003) import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * * @author Hongten</br> * * */public class XlsMain { public static void main(String[] args) throws IOException { XlsMain xlsMain = new XlsMain(); XlsDto xls = null; List<XlsDto> list = xlsMain.readXls(); try { XlsDto2Excel.xlsDto2Excel(list); } catch (Exception e) { e.printStackTrace(); } for (int i = 0; i < list.size(); i++) { xls = (XlsDto) list.get(i); System.out.println(xls.getXh() + " " + xls.getXm() + " " + xls.getYxsmc() + " " + xls.getKcm() + " " + xls.getKcm() + " " + xls.getCj()); } } /** * Read the content of the xls file* * @return List<XlsDto> object* @throws IOException * Input/output (i/o) exception*/ private List<XlsDto> readXls() throws IOException { InputStream is = new FileInputStream("pldrxkxxmb.xls"); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); XlsDto xlsDto = null; List<XlsDto> list = new ArrayList<XlsDto>(); // Loop sheet Sheet for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // Loop row Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } xlsDto = new XlsDto(); // Loop column Cell // 0 Student number 1 Name 2 College 3 Course name 4 Score // for (int cellNum = 0; cellNum <=4; cellNum++) { HSSFCell xh = hssfRow.getCell(0); if (xh == null) { continue; } xlsDto.setXh(getValue(xh)); HSSFCell xm = hssfRow.getCell(1); if (xm == null) { continue; } xlsDto.setXm(getValue(xm)); HSSFCell yxsmc = hssfRow.getCell(2); if (yxsmc == null) { continue; } xlsDto.setYxsmc(getValue(yxsmc)); HSSFCell kcm = hssfRow.getCell(3); if (kcm == null) { continue; } xlsDto.setKcm(getValue(kcm)); HSSFCell cj = hssfRow.getCell(4); if (cj == null) { continue; } xlsDto.setCj(Float.parseFloat(getValue(cj))); list.add(xlsDto); } } return list; } /** * Get the value in the Excel table* * @param hssfCell * Every grid in Excel* @return Values in each grid in Excel */ @SuppressWarnings("static-access") private String getValue(HSSFCell hssfCell) { if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { // Return the value of the boolean type return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { // Return the value of the numeric type return String.valueOf(hssfCell.getNumericCellValue()); } else { // Return the value of the string type return String.valueOf(hssfCell.getStringCellValue()); } } }XlsDto2Excel.java class
//This class is mainly responsible for inserting data into Excel (2003 version) import java.io.FileOutputStream;import java.io.OutputStream;import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class XlsDto2Excel { /** * * @param xls * An object of the XlsDto entity class* @throws Exception * Throws an exception during the import of Excel*/ public static void xlsDto2Excel(List<XlsDto> xls) throws Exception { // Get the total number of columns int CountColumnNum = xls.size(); // Create Excel document HSSFWorkbook hwb = new HSSFWorkbook(); XlsDto xlsDto = null; // sheet corresponds to a work page HSSFSheet sheet = hwb.createSheet("pldrxkxxmb"); HSSFRow firstrow = sheet.createRow(0); // Start the line with subscript 0 HSSFCell[] firstcell = new HSSFCell[CountColumnNum]; String[] names = new String[CountColumnNum]; names[0] = "Student number"; names[1] = "Name"; names[2] = "College"; names[3] = "Course name"; names[4] = "Score"; for (int j = 0; j < CountColumnNum; j++) { firstcell[j] = firstrow.createCell(j); firstcell[j].setCellValue(new HSSFRichTextString(names[j])); } for (int i = 0; i < xls.size(); i++) { // Create a row of HSSFRow row = sheet.createRow(i + 1); // Get every record to be inserted xlsDto = xls.get(i); for (int colu = 0; colu <= 4; colu++) { // Loop in a row HSSFCell xh = row.createCell(0); xh.setCellValue(xlsDto.getXh()); HSSFCell xm = row.createCell(1); xm.setCellValue(xlsDto.getXm()); HSSFCell yxsmc = row.createCell(2); yxsmc.setCellValue(xlsDto.getYxsmc()); HSSFCell kcm = row.createCell(3); kcm.setCellValue(xlsDto.getKcm()); HSSFCell cj = row.createCell(4); cj.setCellValue(xlsDto.getCj());(xlsDto.getMessage()); } } // Create a file output stream and prepare the output spreadsheet OutputStream out = new FileOutputStream("POI2Excel/pldrxkxxmb.xls"); hwb.write(out); out.close(); System.out.println("Database export successful"); } }XlsDto .java class
//This class is an entity class public class XlsDto { /** * Course selection number*/ private Integer xkh; /** * Student number*/ private String xh; /** * Name*/ private String xm; /** * College*/ private String yxsmc; /** * Course number*/ private Integer kch; /** * Course name*/ private String kcm; /** * Score*/ private float cj; public Integer getXkh() { return xkh; } public void setXkh(Integer xkh) { this.xkh = xkh; } public String getXh() { return xh; } public void setXh(String xh) { this.xh = xh; } public String getXm() { return xm; } public void setXm(String xm) { this.xm = xm; } public String getYxsmc() { return yxsmc; } public void setYxsmc(String yxsmc) { this.yxsmc = yxsmc; } public Integer getKch() { return kch; } public void setKch(Integer kch) { this.kch = kch; } public String getKcm() { return kcm; } public void setKcm(String kcm) { this.kcm = kcm; } public float getCj() { return cj; } public void setCj(float cj) { this.cj = cj; } }Background output:
Database export successfully
1.0 hongten School of Information Technology Computer Network Application Basics 80.0
2.0 Wangwu Information Technology College Computer Network Application Basics 81.0
3.0 Li Shengji School of Information Technology Computer Network Application Basics 82.0
4.0 Basics of Computer Network Application of Class 5 College of Information Technology 83.0
5.0 Cai Shiyun School of Information Technology Computer Network Application Basics 84.0
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.