/**
* excel文件导入、导出、下载工具类
*/
public class ExcelTools extends ExcelUtils {
private static final String CONTENT_TYPE = "application/vnd.ms-excel";
private static final String HEADER_DIS = "Content-Disposition";
private static final String HEADER_ATT = "attachment;filename=";
private static final String FILE_EX = ".xls";
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
private int readWhichSheet; // 读取哪一个sheet
private int startReadRowNum;// 从第几行开始读取
private int endReadRowNum; // 读到第几行结束
/**
* excel文件生成并下载
*
* @param servletActionContext 上下文
* @param config 模板文件
* @param fname 导出时的文件名
* @throws ExcelException
* @throws IOException
*/
@SuppressWarnings("static-access")
public static void export2Web(HttpServletResponse response, String config,
String fname) throws ExcelException, IOException {
response.reset();
response.setContentType(CONTENT_TYPE);
response.setHeader(HEADER_DIS, HEADER_ATT + fname + FILE_EX);
export(config, response.getOutputStream());
}
/**
* 将excel生成至本地
*
* @param config 模板文件
* @param dir 输出的文件目录
* @param fname 输出的文件名
* @throws ExcelException
* @throws IOException
*/
@SuppressWarnings("static-access")
public static void export2Local(String config, String dir, String fname)
throws ExcelException, IOException {
export(config, new FileOutputStream(dir + "/" + fname));
}
/**加载EXCEL文件
* @param excelfile excel文件
* @param readWhichSheet 需要解析哪个sheet表，座标1开始
* @param startReadRowNum 需要从哪一行开始解析,座标1开始
* @param endReadRowNum 结束到哪一行，输入0则解析到末尾
* @throws FileNotFoundException
* @throws IOException
*/
public void loadExcelFile(File excelfile, int readWhichSheet,
int startReadRowNum, int endReadRowNum)
throws FileNotFoundException, IOException {
fs = new POIFSFileSystem(new FileInputStream(excelfile));
wb = new HSSFWorkbook(fs);
this.readWhichSheet = readWhichSheet - 1;
this.startReadRowNum = startReadRowNum - 1;
this.endReadRowNum = endReadRowNum - 1;
}
/**加载EXCEL文件
* @param inputStream excel文件流
* @param readWhichSheet 需要解析哪个sheet表，座标1开始
* @param startReadRowNum 需要从哪一行开始解析,座标1开始
* @param endReadRowNum 结束到哪一行，输入0则解析到末尾
* @throws FileNotFoundException
* @throws IOException
*/
public void loadExcelFile(InputStream inputStream, int readWhichSheet,
int startReadRowNum, int endReadRowNum)
throws FileNotFoundException, IOException {
fs = new POIFSFileSystem(inputStream);
wb = new HSSFWorkbook(fs);
this.readWhichSheet = readWhichSheet;
this.startReadRowNum = startReadRowNum;
this.endReadRowNum = endReadRowNum;
}
/**
* 读取excel的值，以List<String[]>返回
* @return
*/
public List<String[]> readExcelData() {
int allsheet = wb.getNumberOfSheets();
if (readWhichSheet >= allsheet) {
return new ArrayList<String[]>(0);
}
HSSFSheet sheet = wb.getSheetAt(readWhichSheet);
if (sheet == null) {
return new ArrayList<String[]>(0);
}
int rowNum = sheet.getLastRowNum() + 1; // 这里比较特殊，cell个数计算正确，row个数需要加1才对
System.out.println("rownum:" + rowNum);
if (endReadRowNum == -1)
endReadRowNum = rowNum - 1;// 如果结束行等0，那么就取到末尾
List<String[]> resultList = new ArrayList<String[]>(rowNum);
for (int i = startReadRowNum; i <= endReadRowNum && i < rowNum; i++) {
HSSFRow row = sheet.getRow(i);
if (row == null) {
break;
}
short cellsnum = row.getLastCellNum();
String[] cells = new String[cellsnum];
for (int k = 0; k < cellsnum; k++) {
HSSFCell cell = row.getCell((short) k);
Object val = getCellValue(cell);
cells[k] = val == null || "".equals(val.toString()) ? "无" : val.toString();
}
resultList.add(cells);
}
return resultList;
}
/**
* 根据Cell类型返回正确的值
*
* @param cell
* @return
*/
public static Object getCellValue(HSSFCell cell) {
if ((cell == null) || (HSSFCell.CELL_TYPE_BLANK == cell.getCellType())) {
return "";
} else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
return cell.getBooleanCellValue();
} else if (HSSFCell.CELL_TYPE_FORMULA == cell.getCellType()) {
return cell.getCellFormula();
} else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return DateUtil.getDate2Str(cell.getDateCellValue(), null);
} else {
return cell.getNumericCellValue();
}
} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
return cell.getRichStringCellValue().getString();
} else {
return cell.getRichStringCellValue().getString();
}
}
/**
* 根据Cell类型返回正确的值
*
* @param cell
* @return
*/
public static String getCellValueAsString(HSSFCell cell) {
if (cell == null) {
return null;
} else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) {
return "";
} else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
return String.valueOf(cell.getBooleanCellValue());
} else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return DateUtil.getDate2Str(cell.getDateCellValue(), null);
} else {
DecimalFormat df = new DecimalFormat("#00");
return df.format(cell.getNumericCellValue());
}
} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
return cell.getRichStringCellValue().getString();
} else {
return cell.getRichStringCellValue().getString();
}
}
}
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
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 XlsMain {
public static void main(String[] args) throws IOException {
XlsMain xlsMain = new XlsMain();
xlsMain.readXls("f:\\demo.xls");
}
private void readXls(String filename) throws IOException {
InputStream is = new FileInputStream(filename);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
// 循环列Cell
for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
HSSFCell hssfCell = hssfRow.getCell(cellNum);
if (hssfCell == null) {
System.out.print(" " + "null");
continue;
}
System.out.print(" " + getValue(hssfCell));
}
System.out.println();
}
}
}
@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());
}
}
}
import java.io.IOException;
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;
public class XlsxMain {
public static void main(String[] args) throws IOException {
XlsxMain xlsxMain = new XlsxMain();
xlsxMain.readXlsx("e:/a.xlsx");
}
private void readXlsx(String filename) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(filename);
// 循环工作表Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
// 循环列Cell
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
if (xssfCell == null) {
continue;
}
System.out.print(" " + getValue(xssfCell));
}
System.out.println();
}
}
}
@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 {
try {
return String.valueOf(xssfCell.getStringCellValue());
} catch (Exception e) {
return "null";
}
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
