//ExcelHelper .java
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
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;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.nsn.entity.ExcelRowVo;
import com.nsn.entity.NameLikeExcel;
/**
* excel帮助类
* @author zhujian
*
*/
public class ExcelHelper {
// private static Logger logger = Logger.getLogger(ExcelHelper.class);
private static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
private static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
private static final String EMPTY = "";
private static final String POINT = ".";
public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
public static final String PROCESSING = "Processing...";
/**
* 解析以".xls"\".xlsx"后缀的excel
* @param readPath excel路径
* @param ExcelVoClass 实体类的Class
* @param cellNum 数据列的数量
* @return 实体类的集合
* @throws IOException
* @throws InstantiationException
* @throws IllegalAccessException
*/
public List<ExcelRowVo> readExcel(String readPath, Class ExcelVoClass,
int cellNum) throws IOException, InstantiationException,
IllegalAccessException {
return readExcel(readPath, ExcelVoClass, cellNum, 0, 0);
}
/**
* 解析以".xls"\".xlsx"后缀的excel
* @param readPath excel路径
* @param ExcelVoClass 实体类的Class
* @param cellNum 数据列的数量
* @param startRowNum 数据记录行的索引，从零行开始
* @return 实体类的集合
* @throws IOException
* @throws InstantiationException
* @throws IllegalAccessException
*/
public List<ExcelRowVo> readExcel(String readPath, Class ExcelVoClass,
int cellNum, int startRowNum) throws IOException,
InstantiationException, IllegalAccessException {
return readExcel(readPath, ExcelVoClass, cellNum, startRowNum, 0);
}
/**
* 解析以".xls"\".xlsx"后缀的excel
* @param readPath excel路径
* @param ExcelVoClass 实体类的Class
* @param cellNum 数据列的数量
* @param startRowNum 数据记录行的索引，从零行开始
* @param startCellNum 数据记录列的索引，从零列开始
* @return 实体类的集合
* @throws IOException
* @throws InstantiationException
* @throws IllegalAccessException
*/
public List<ExcelRowVo> readExcel(String readPath, Class ExcelVoClass,
int cellNum, int startRowNum, int startCellNum) throws IOException,
InstantiationException, IllegalAccessException {
if (null == readPath || EMPTY.endsWith(readPath)
|| null == ExcelVoClass || cellNum <= 0) {
return null;
} else {
String postfix = getPostfix(readPath);
if (!EMPTY.equals(postfix)) {
if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
return readXls(readPath, ExcelVoClass, cellNum,
startRowNum, startCellNum);
} else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
return readXlsx(readPath, ExcelVoClass, cellNum,
startRowNum, startCellNum);
}
} else {
System.out.println(readPath + NOT_EXCEL_FILE);
}
}
return null;
}
/**
* 解析".xls"结束的excel
* @param readPath excel路径
* @param ExcelVoClass 实体类的Class
* @param cellNum 数据列的数量
* @param startRowNum 数据记录行的索引，从零行开始
* @param startCellNum 数据记录列的索引，从零列开始
* @return 实体类的集合
* @throws IOException
* @throws InstantiationException
* @throws IllegalAccessException
*/
private List<ExcelRowVo> readXls(String readPath, Class ExcelVoClass,
int cellNum, int startRowNum, int startCellNum) throws IOException,
InstantiationException, IllegalAccessException {
System.out.println(PROCESSING + readPath);
InputStream is = new FileInputStream(readPath);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
ExcelRowVo vo = null;
String[] values = null;
List<ExcelRowVo> list = new ArrayList<ExcelRowVo>();
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = startRowNum; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
vo = (ExcelRowVo) ExcelVoClass.newInstance();
values = new String[cellNum];
for (int index = startCellNum; index < cellNum; index++) {
values[index] = getValue(hssfRow.getCell(index));
}
vo.setValues(values);
list.add(vo);
}
}
}
return list;
}
/**
* 解析".xlsx"结束的excel
* @param readPath excel路径
* @param ExcelVoClass 实体类的Class
* @param cellNum 数据列的数量
* @param startRowNum 数据记录行的索引，从零行开始
* @param startCellNum 数据记录列的索引，从零列开始
* @return 实体类的集合
* @throws IOException
* @throws InstantiationException
* @throws IllegalAccessException
*/
private List<ExcelRowVo> readXlsx(String readPath, Class ExcelVoClass,
int cellNum, int startRowNum, int startCellNum) throws IOException,
InstantiationException, IllegalAccessException {
System.out.println(PROCESSING + readPath);
InputStream is = new FileInputStream(readPath);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
ExcelRowVo vo = null;
String[] values = null;
List<ExcelRowVo> list = new ArrayList<ExcelRowVo>();
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = startRowNum; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
vo = (ExcelRowVo) ExcelVoClass.newInstance();
values = new String[cellNum];
for (int index = startCellNum; index < cellNum; index++) {
values[index] = getValue(xssfRow.getCell(index));
}
vo.setValues(values);
list.add(vo);
}
}
}
return list;
}
/**
* ".xlsx"结束的excel的列值
* @param xssfCell XSSFCell
* @return String
*/
@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfCell) {
if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfCell.getBooleanCellValue());
} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfCell.getNumericCellValue());
} else {
return String.valueOf(xssfCell.getStringCellValue());
}
}
/**
* ".xls"结束的excel的列值
* @param hssfCell HSSFCell
* @return String
*/
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
/**
* get postfix of the path
*
* @param path
* @return
*/
private static String getPostfix(String path) {
if (path == null || EMPTY.equals(path.trim())) {
return EMPTY;
}
if (path.contains(POINT)) {
return path.substring(path.lastIndexOf(POINT) + 1, path.length());
}
return EMPTY;
}
/**
* 写入以".xlsx"后缀的excel
* @param writePath 输出路径
* @throws IOException
*/
public void writeExcel(String[] heads, List<ExcelRowVo> listData,
String writePath) throws IOException {
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet xssfSheet = workBook.createSheet();
Font font = createFonts(workBook);
getHead(workBook, xssfSheet, heads, font);
// 第二行开始记录数据
int l = 1;
for (int i = 0; i < listData.size(); i++) {
XSSFRow xssRow = xssfSheet.createRow(l++);
ExcelRowVo vo = listData.get(i);
String[] values = vo.getValues();
for (int j = 0; j < values.length; j++) {
XSSFCell xssfCell = xssRow.createCell(j);
xssfCell.setCellValue(values[j]);
}
}
FileOutputStream out = new FileOutputStream(writePath);
workBook.write(out);
workBook.close();
System.out.println("Processing..." + writePath);
}
/**
* 表头——可根据实际情况重载方法
* @param xssfSheet excel页
* @param heads 表头名称
* @param font style
* @return 表头行
*/
private void getHead(XSSFWorkbook workBook, XSSFSheet xssfSheet, String[] heads, Font font) {
XSSFRow xssRow = xssfSheet.createRow(0);
CellStyle cellStyle = workBook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setFillPattern(CellStyle.FINE_DOTS );
cellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
cellStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
for (int i = 0; i < heads.length; i++) {
XSSFCell xssfCell = xssRow.createCell(i);
xssfCell.setCellValue(heads[i]);
xssfCell.setCellStyle(cellStyle);
}
}
public Font createFonts(XSSFWorkbook workBook){
Font font = workBook.createFont();
font.setFontName("宋体");
// //设置字体颜色-yellow
// font.setColor((short) 43);
// //cell高度
// font.setFontHeight((short) 2000);
// //加粗
// font.setBold(false);
// //斜体
// font.setItalic(false);
return font;
}
/**
* @param args
* @throws IllegalAccessException
* @throws InstantiationException
*/
public static void main(String[] args) throws InstantiationException,
IllegalAccessException {
// String path = "lib/student_info.xlsx";
String path = "名称模糊匹配.xlsx";
try {
ExcelHelper help = new ExcelHelper();
List<ExcelRowVo> list = help
.readExcel(path, NameLikeExcel.class, 5, 1);
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i).getValuesToString());
NameLikeExcel bean = (NameLikeExcel) list.get(i);
bean.setPoiName("POI名称");
}
String[] heads = new String[]{"名称","地市","区县","纬度","经度","POI名称","POI纬度","POI经度","距离","打分"};
help.writeExcel(heads, list, "lib/输出.xlsx");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
http://www.pudn.com/Download/item/id/2798913.html
浙公网安备 33010602011771号