Java poi read excel action sample (2 pieces of code)
- 2020-04-01 02:31:04
- OfStack
The project requires reading the contents of the excel file and converting it to XML format. POI and JExcelAPI are two tools commonly used to read excel documents. Here we introduce the POI implementation for reading excel documents.
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
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 ReadExcel {
public static void main(String[] args)throws Exception {
read("d:\demo.xls");
}
public static ArrayList read(String fileName){
ArrayList list = new ArrayList();
String sql = "";
try{
File f = new File(fileName);
FileInputStream fis = new FileInputStream(f);
HSSFWorkbook wbs = new HSSFWorkbook(fis);
HSSFSheet childSheet = wbs.getSheetAt(0);
System.out.println(" The number of rows :" + childSheet.getLastRowNum());
for(int i = 4;i<childSheet.getLastRowNum();i++){
HSSFRow row = childSheet.getRow(i);
System.out.println(" The number of columns :" + row.getPhysicalNumberOfCells());
if(null != row){
for(int k=1;k<row.getPhysicalNumberOfCells();k++){
HSSFCell cell;
cell = row.getCell((short)k);
// System.out.print(getStringCellValue(cell) + "t");
list.add(getStringCellValue(cell) + "t");
}
}
}
}catch(Exception e){
e.printStackTrace();
}
return list;
}
private static String getStringCellValue(HSSFCell cell) {
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
}
Let's do another example
package edu.sjtu.erplab.poi;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class ExcelReader {
private POIFSFileSystem fs;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private HSSFRow row;
public String[] readExcelTitle(InputStream is) {
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);
//Total number of header columns
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
//title[i] = getStringCellValue(row.getCell((short) i));
title[i] = getCellFormatValue(row.getCell((short) i));
}
return title;
}
public Map<Integer, String> readExcelContent(InputStream is) {
Map<Integer, String> content = new HashMap<Integer, String>();
String str = "";
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
//You get the total number of rows
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
//The body should begin with the second line, the first with the header heading
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
//The data content of each cell is split by "-" and then restored by the String class's replace() method as needed
//You can also set the data for each cell to a javabean property, where you need to create a new javabean
// str += getStringCellValue(row.getCell((short) j)).trim() +
// "-";
str += getCellFormatValue(row.getCell((short) j)).trim() + " ";
j++;
}
content.put(i, str);
str = "";
}
return content;
}
private String getStringCellValue(HSSFCell cell) {
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
private String getDateCellValue(HSSFCell cell) {
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[ years ]", "-").replace(" day ", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
System.out.println(" Incorrect date format !");
e.printStackTrace();
}
return result;
}
private String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
//Determines the Type of the current Cell
switch (cell.getCellType()) {
//If the current Cell is of Type NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
//Determines whether the current cell is Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//If it is of type Date, convert to Data format
//Method 1: the data format looks like this with time and seconds: 2011-10-12 0:00:00
//cellvalue = cell.getDateCellValue().toLocaleString();
//Method 2: this data format has no time and seconds: 2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
//If it's a pure number
else {
//Gets the value of the current Cell
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
//If the Type of the current Cell is STRIN
case HSSFCell.CELL_TYPE_STRING:
//Gets the current Cell string
cellvalue = cell.getRichStringCellValue().getString();
break;
//The default Cell value
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
public static void main(String[] args) {
try {
//Test for reading Excel spreadsheet titles
InputStream is = new FileInputStream("d:\test2.xls");
ExcelReader excelReader = new ExcelReader();
String[] title = excelReader.readExcelTitle(is);
System.out.println(" To obtain Excel Table titles :");
for (String s : title) {
System.out.print(s + " ");
}
//Test for reading Excel spreadsheet contents
InputStream is2 = new FileInputStream("d:\test2.xls");
Map<Integer, String> map = excelReader.readExcelContent(is2);
System.out.println(" To obtain Excel Table contents :");
for (int i = 1; i <= map.size(); i++) {
System.out.println(map.get(i));
}
} catch (FileNotFoundException e) {
System.out.println(" The file for the specified path was not found !");
e.printStackTrace();
}
}
}