Java common tool class Excel operation class and dependency package download
- 2020-04-01 03:21:20
- OfStack
Dependent package download: (link: http://xiazai.jb51.net/201407/tools/java-excel-dependency (jb51.net). Rar)
Excel tool class ExcelUtil. Java source code:
package com.itjh.javaUtil;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.LinkedList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
/**
* Encapsulation of excel Operations, including local reads and writes excel And output in the stream excel, support office 2007 . <br/>
* Depends on the poi-3.9-20121203.jar,poi-ooxml-3.9-20121203.jar,poi-ooxml-schemas-3.9-
* 20121203.jar,dom4j-1.6.1.jar<br/>
* The argument constructor parameter is excel The full path of <br/>
*
* @author Song Lijun
* @date 2014 years 07 month 03 day
*/
public class ExcelUtil {
//Excel file path
private String path = "";
//Whether column widths are automatically extended to match content when writing to excel.
private boolean autoColumnWidth = false;
public ExcelUtil() {
}
public ExcelUtil(String path) {
this.path = path;
}
public List<Object[]> read(int sheetOrder) throws FileNotFoundException,
IOException, InvalidFormatException {
FileInputStream fis = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(fis);
if (fis != null) {
fis.close();
}
Sheet sheet = workbook.getSheetAt(sheetOrder);
//Used to record excel values
List<Object[]> valueList = new LinkedList<Object[]>();
//Loop through each row, each column.
for (Row row : sheet) {
//Each line
Object[] rowObject = null;
for (Cell cell : row) {
//Cell.getcelltype is the type that gets the value stored in the cell
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
//Method to get a Boolean object
rowObject = CollectionUtil.addObjectToArray(rowObject,
cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
//Let's see if it's a date format
if (DateUtil.isCellDateFormatted(cell)) {
//Read date format
rowObject = CollectionUtil.addObjectToArray(rowObject,
cell.getDateCellValue());
} else {
DecimalFormat df = new DecimalFormat();
//The value of the cell, instead of,
String value = df.format(cell.getNumericCellValue())
.replace(",", "");
//Read the Numbers
rowObject = CollectionUtil.addObjectToArray(rowObject,
value);
}
break;
case Cell.CELL_TYPE_FORMULA:
//Read the formula
rowObject = CollectionUtil.addObjectToArray(rowObject,
cell.getCellFormula());
break;
case Cell.CELL_TYPE_STRING:
//Reads a String
rowObject = CollectionUtil.addObjectToArray(rowObject, cell
.getRichStringCellValue().toString());
break;
}
}
//Add this row to the list.
valueList.add(rowObject);
}
return valueList;
}
public String read(int sheetOrder, int colum, int row) throws Exception {
FileInputStream fis = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(fis);
if (fis != null) {
fis.close();
}
Sheet sheet = workbook.getSheetAt(sheetOrder);
Row rows = sheet.getRow(row - 1);
Cell cell = rows.getCell(colum - 1);
String content = cell.getStringCellValue();
return content;
}
public void write(int sheetOrder, int colum, int row, String content)
throws Exception {
FileInputStream fis = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(fis);
if (fis != null) {
fis.close();
}
Sheet sheet = workbook.getSheetAt(sheetOrder);
Row rows = sheet.createRow(row - 1);
Cell cell = rows.createCell(colum - 1);
cell.setCellValue(content);
FileOutputStream fileOut = new FileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
}
public int getSheetLastRowNum(int sheetOrder) throws IOException,
InvalidFormatException {
FileInputStream fis = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(fis);
if (fis != null) {
fis.close();
}
Sheet sheet = workbook.getSheetAt(sheetOrder);
return sheet.getLastRowNum();
}
public void makeExcel(String sheetName, String[] fieldName,
List<Object[]> data) throws IOException {
//Generate the workbook in memory
HSSFWorkbook workbook = makeWorkBook(sheetName, fieldName, data);
//Intercept folder path
String filePath = path.substring(0, path.lastIndexOf("\"));
//If the path does not exist, create the path
File file = new File(filePath);
// System.out.println(path+"-----------"+file.exists());
if (!file.exists())
file.mkdirs();
FileOutputStream fileOut = new FileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
}
public void makeStreamExcel(String excelName, String sheetName,
String[] fieldName, List<Object[]> data,
HttpServletResponse response) throws IOException {
OutputStream os = null;
response.reset(); //Empty output stream
os = response.getOutputStream(); //Get the output stream
response.setHeader("Content-disposition", "attachment; filename="
+ new String(excelName.getBytes(), "ISO-8859-1")); //Sets the output file header
response.setContentType("application/msexcel"); //Define the output type
//Generate the workbook in memory
HSSFWorkbook workbook = makeWorkBook(sheetName, fieldName, data);
os.flush();
workbook.write(os);
}
private HSSFWorkbook makeWorkBook(String sheetName, String[] fieldName,
List<Object[]> data) {
//Used to record the maximum column width, automatically adjust the column width.
Integer collength[] = new Integer[fieldName.length];
//Generates workbook objects
HSSFWorkbook workbook = new HSSFWorkbook();
//Generate the worksheet object
HSSFSheet sheet = workbook.createSheet();
//To enable the worksheet to support Chinese, set the character set to UTF_16
workbook.setSheetName(0, sheetName);
//Generate a line of
HSSFRow row = sheet.createRow(0);
//Generate cell
HSSFCell cell;
//Write the name of each field
for (int i = 0; i < fieldName.length; i++) {
//Creates the first row of cells with the names of each field
cell = row.createCell((short) i);
//Sets the cell's content to string
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//To be able to enter Chinese in a cell, set the character set to UTF_16
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
//Assign a value to the contents of the cell
cell.setCellValue(new HSSFRichTextString(fieldName[i]));
//Initializes the column width
collength[i] = fieldName[i].getBytes().length;
}
//Temporary cell content
String tempCellContent = "";
//Write each record, each of which corresponds to a row in the excel sheet
for (int i = 0; i < data.size(); i++) {
Object[] tmp = data.get(i);
//Generate a line of
row = sheet.createRow(i + 1);
for (int j = 0; j < tmp.length; j++) {
cell = row.createCell((short) j);
//Set the cell character type to String
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
tempCellContent = (tmp[j] == null) ? "" : tmp[j].toString();
cell.setCellValue(new HSSFRichTextString(tempCellContent));
//If the column width is automatically adjusted.
if (autoColumnWidth) {
if (j >= collength.length) { //When the number of header columns is less than the number of data columns.
collength = CollectionUtil.addObjectToArray(collength,
tempCellContent.getBytes().length);
} else {
//If the width of the content is greater than the previous maximum, set the width accordingly.
if (collength[j] < tempCellContent.getBytes().length) {
collength[j] = tempCellContent.getBytes().length;
}
}
}
}
}
//Automatically adjusts the column width.
if (autoColumnWidth) {
//Adjust the maximum width for this column of text.
for (int i = 0; i < fieldName.length; i++) {
sheet.setColumnWidth(i, collength[i] * 2 * 256);
}
}
return workbook;
}
public void setAutoColumnWidth(boolean autoColumnWidth) {
this.autoColumnWidth = autoColumnWidth;
}
}