Java implements the method of reading and generating Excel files

This article describes the Java method to read and generate Excel files. Share it for your reference, as follows:

1. Read Excel files

You need to download poi-3.0.1-FINAL-20070705.jar first (click here to download poi-3.0.1-FINAL-20070705.jar .)

ExcelExamRead.java

 import java.io.File;import java.io.FileInputStream;import java.io.IOException;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;public class ExcelExamRead {/** Read Excel file content*/public void showExcel(String excelName) {File file = new File(excelName);FileInputStream in = null;try {// Create a reference to Excel workbook file in = new FileInputStream(file);HSSFWorkbook hwb = new HSSFWorkbook(in);HSSFSheet sheet = hwb.getSheet("myFirstExcel");// Reference a valid worksheet in this Excel based on the specified name// Read the data of the Excel worksheet System.out.println("Below is the Excel file" + file.getAbsolutePath() + "Content:");HSSFRow row = null;HSSFCell cell = null;int rowNum = 0; // Row label int colNum = 0; // Column label for (; rowNum < 9; rowNum++) {// Get rowNum row = sheet.getRow((short) rowNum); for (colNum = 0; colNum < 5; colNum++) {cell = row.getCell((short) colNum);// Create a cell object according to the position of the current row System.out.print(cell.getStringCellValue() + "/t");// Get the content in the current cell}System.out.println(); // line break}in.close();} catch (Exception e) {System.out.println("Read Excel file" + file.getAbsolutePath() + "Failed:" + e);} finally {if (in != null) {try {in.close();} catch (IOException e1) {}}}} public static void main(String[] args) {ExcelExamRead excel = new ExcelExamRead();String excelName = "D:/ExcelExamRead.xls";excel.showExcel(excelName);}}

2. Generate Excel file

ExcelExamWrite.java:

 import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.Region;//Create Excel file public class ExcelExamWrite {// Create a new Excel file, add 5 rows and 5 columns contents, and add a large cell that merges 2 rows and 5 columns and 5 merge cells that merge 2 rows and 1 column. public void createExcel(String fileName) {File file = new File(fileName);// Create excel file object FileOutputStream fOut = null;try {// Create a new HSSFWorkbook object HSSFWorkbook workbook = new HSSFWorkbook();// Create an Excel worksheet, you can specify the name of the worksheet HSSFSheet sheet = workbook.createSheet("myFirstExcel");// Create a font, red and bold HSSFFont font = workbook.createFont();font.setColor(HSSFFont.COLOR_RED);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);HSSFFont font1 = workbook.createFont();// Create font, black and non-bold font1.setColor(HSSFFont.COLOR_NORMAL);font1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// Create cell format, such as centering, left alignment, etc. HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // Align in the horizontal direction center// Align in the vertical direction center cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);cellStyle.setFont(font); // Set font HSSFCellStyle cellStyle1 = workbook.createCellStyle();cellStyle1.setAlignment(HSSFCellStyle.ALIGN_LEFT);cellStyle1.setFont(font1);// Below will create a 4 rows and 3 columns table. The first behavior is the header. int rowNum = 0;// row mark int colNum = 0;// column mark // Create header information HSSFRow row = sheet.createRow((short) rowNum); // Create row at index 0 HSSFCell cell = null; // Cell for (colNum = 0; colNum < 5; colNum++) {// Create cell on the colNum column of the current row cell = row.createCell((short) colNum);// Define the cell as a character type, or it can be specified as a date type and a numeric type cell.setCellType(HSSFCell.CELL_TYPE_STRING);// Define encoding method. In order to support Chinese, ENCODING_UTF_16cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle); // Format cell.setCellValue("Table header-thread" + (colNum + 1) + "column"); // Add content to cell}rowNum++; for (; rowNum < 5; rowNum++) {// Create a new rowNum row = sheet.createRow((short) rowNum); for (colNum = 0; colNum < 5; colNum++) {// Create a cell at the colNum position of the current row cell = row.createCell((short) colNum);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle1);cell.setCellValue("Table body-th" + rowNum + "rowth" + (colNum + 1)+ "column");}}// Merge cells // First create cells with 2 rows and 5 columns, and then merge these cells into 2 large cells rowNum = 5;for (; rowNum < 9; rowNum++) {row = sheet.createRow((short) rowNum);for (colNum = 0; colNum < 5; colNum++) {// Create cell at the colNum position of the current row cell = row.createCell((short) colNum);}}// Create the first large cell with height 2 and width 2 rowNum = 5;colNum = 0;Region region = new Region(rowNum, (short) colNum, (rowNum + 1),(short) (colNum + 4));sheet.addMergedRegion(region);// Get the first large cell cell = sheet.getRow(rowNum).getCell((short) colNum);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle);cell.setCellValue("merge row cells");// Create the second large cell with height 2 and width 3 rowNum = 7; for (colNum = 0; colNum < 5; colNum++) {region = new Region(rowNum, (short) colNum, (rowNum + 1),(short) (colNum));sheet.addMergedRegion(region);// Get the second large cell cell = sheet.getRow(rowNum).getCell((short) colNum);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle);cell.setCellValue("Merge column cells");}// Create a new output file stream fOut = new FileOutputStream(file);// Write the created content to the specified Excel file workbook.write(fOut);fOut.flush();fOut.close();// The operation ends, close the file System.out.println("Excel file creation successfully! /nThe storage path of the Excel file is: "+ file.getAbsolutePath());} catch (Exception e) {System.out.println("Excel file" + file.getAbsolutePath()+ "Create failed/nThe reason is: " + e);} finally {if (fOut != null) {try {fOut.close();} catch (IOException e1) {}}}} public static void main(String[] args) throws Exception {ExcelExamWrite excel = new ExcelExamWrite();String fileName = "e:/ExcelExamWrite.xls";excel.createExcel(fileName);}}

For more information about Java related content, please check out the topics of this site: "Summary of Java Operation Excel Skills", "Summary of Java+MySQL Database Programming", "Tutorial on Java Data Structure and Algorithm", "Summary of Java File and Directory Operation Skills" and "Summary of Java Operation DOM Node Skills"

I hope this article will be helpful to everyone's Java programming.