java分页_Java分页下载

packagecom.wjs.common.util.excel;importjava.io.BufferedInputStream;importjava.io.ByteArrayInputStream;importjava.io.ByteArrayOutputStream;importjava.io.FileNotFoundException;importjava.io.IOException;importjava.io.InputStream;importjava.io.OutputStream;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Collection;importjava.util.Date;importjava.util.Iterator;importjava.util.List;importjava.util.Map;importjavax.servlet.http.HttpServletResponse;importorg.apache.commons.beanutils.BeanMap;importorg.apache.log4j.Logger;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFRichTextString;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.springframework.util.CollectionUtils;/***

*@authorliqiang05

**/

public classExcelUtils {/*** logger*/

private static final Logger logger = Logger.getLogger(ExcelUtils.class);/*** 设置列格式

*

*@paramsheet

*@paramcolumnWidthList*/

public static boolean setSheetStyle(Sheet sheet, ListcolumnWidthList) {if (sheet == null || columnWidthList == null) {return false;

}//设置所有列的宽度

for (int indx = 0, iMax = columnWidthList.size(); indx < iMax; indx++) {

Integer columnWidth=columnWidthList.get(indx);if (columnWidth == null) {continue;

}

sheet.setColumnWidth(indx, columnWidth.intValue()* 256);

}return true;

}/*** 设置行信息

*

*@paramrow

*@paramrowObj

*@return

*/

public static boolean setRowInfo(Row row, CollectionrowObj) {if (row == null || rowObj == null) {if(logger.isInfoEnabled()) {

logger.info("Row:" + row + ",rowObj" +rowObj);

}return false;

}//填充每一列数据

int indxColumn = 0;for(Object object : rowObj) {

Cell cell= row.createCell(indxColumn++);if (object == null) {if(logger.isDebugEnabled()) {

logger.debug("Row:" + row + ",Column:" + indxColumn + ",is empty");

}continue;

}

String columnValue=object.toString();

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

cell.setCellValue(columnValue);

}return true;

}/*** 设置行信息

*

*@paramrow

*@paramrowObj

*@paramconvert*/@SuppressWarnings({"rawtypes", "unchecked"})public static booleansetRowInfo(Row row, Object rowObj, IExcelConvert convert) {if (row == null || rowObj == null) {if(logger.isInfoEnabled()) {

logger.info("Row:" + row + ",rowObj" +rowObj);

}return false;

}try{

Collection rowContent = null;if (convert != null) {

rowContent=convert.convert(rowObj);

}else if (rowObj instanceofMap) {

rowContent=((Map) rowObj).values();

}else if (rowObj instanceofCollection) {

rowContent=(Collection) rowObj;

}else{

rowContent= (newBeanMap(rowObj)).values();

}if (rowContent == null ||rowContent.isEmpty()) {if(logger.isDebugEnabled()) {

logger.debug("Row:" + row + ",is empty");

}return false;

}returnsetRowInfo(row, rowContent);

}catch(Throwable e) {

logger.info(rowObj+ "convertFailed,row:" +row, e);return false;

}

}/*** 将数据写入excel

*

*@paramsheet

*@paramcolumnWidth

*@paramheader

*@paramcontent

*@return

*/

public static boolean setExcelInfo(Sheet sheet, List columnWidth, List header, List>rows) {return setExcelInfo(sheet, columnWidth, header, rows, null);

}/*** 将数据写入excel

*

*@paramsheet

*@paramcolumnWidth

*@paramheader

*@paramcontent

*@paramconverter

*@return

*/

public static boolean setExcelInfo(Sheet sheet, List columnWidth, List header, List>content, IExcelConvert converter) {if (sheet == null) {

logger.info("sheet is null");return false;

}//设置sheet格式

setSheetStyle(sheet, columnWidth);//设置头信息

int indxRow = 0;

Row row= sheet.createRow(indxRow++);

setRowInfo(row, header,null);//如果内容为空 则退出

if (content == null ||content.isEmpty()) {

logger.info("content is null,cannot write excel");return true;

}for(Object rowContent : content) {

row= sheet.createRow(indxRow++);

setRowInfo(row, rowContent, converter);

}return true;

}/*** 導出到excel

*

*@paramtitle

* sheet Title

*@paramcolumnWidthList

* 所有列的寬度,可以不指定

*@paramcontent

* 內容, 每一項為一行,每一行內是List代表所有列

*@return

*/

public static Workbook setupXls(String title, List columnWidthList, List>content) {

Workbook wb= newHSSFWorkbook();

Sheet sheet=wb.createSheet(title);if (columnWidthList != null) {//设置所有列的宽度

for (int indx = 0, iMax = columnWidthList.size(); indx < iMax; indx++) {

Integer columnWidth=columnWidthList.get(indx);if (columnWidth == null) {continue;

}

sheet.setColumnWidth(indx, columnWidth.intValue()* 256);

}

}if (content == null ||content.isEmpty()) {if(logger.isInfoEnabled()) {

logger.info("content is null,cannot write excel,title:" +title);

}returnwb;

}//遍歷一行

for (int indxRow = 0, iMaxRow = content.size(); indxRow < iMaxRow; indxRow++) {

Row row=sheet.createRow(indxRow);

List rowContent =content.get(indxRow);if (rowContent == null ||rowContent.isEmpty()) {if(logger.isDebugEnabled()) {

logger.debug("Row:" + indxRow + ",is empty,title:" +title);

}continue;

}//填充每一列数据

for (int indxColumn = 0, iMaxColumn = rowContent.size(); indxColumn < iMaxColumn; indxColumn++) {

Cell cell=row.createCell(indxColumn);

String columnValue=rowContent.get(indxColumn);if (columnValue == null || columnValue.length() == 0) {if(logger.isDebugEnabled()) {

logger.debug("Row:" + indxRow + ",Column:" + indxColumn + ",is empty,title:" +title);

}continue;

}

cell.setCellValue(columnValue);

}

}returnwb;

}/*** 加載Excel 默認實現方式

*

*@paramwb

*@return

*/

public static List>loadXls(Workbook wb) {//默認 只讀第一個sheet, 且從第二行開始遍歷,默认读取到最大列

return loadXls(wb, 0, 1, 0);

}/*** 加载excel

*

*@paramwb

*@paramsheetIndx

* 要加载excel的sheet页的index

*@paramstartRowIndx

* 要加载Row的index

*@paramiMaxColumn

* 最大读到Cloumn的index

*@returnList>*/

public static List> loadXls(Workbook wb, int sheetIndx, int startRowIndx, intiMaxColumn) {

List> resList = new ArrayList>();if (wb == null || sheetIndx < 0 || startRowIndx < 0 || iMaxColumn < 0) {

logger.error("param error,return empty list,Workbook:" + wb + ",sheetIndex:" + sheetIndx + ",startRowNo:" + startRowIndx + ",iMaxColumn:" +iMaxColumn);returnresList;

}

Sheet sheet=wb.getSheetAt(sheetIndx);if (sheet == null) {

logger.error("sheet is null,return empty list,Workbook:" + wb + ",sheetIndex:" + sheetIndx + ",startRowNo:" +startRowIndx);returnresList;

}//從指定行開始遍歷

for (int indxRow = startRowIndx, iMaxRow = sheet.getLastRowNum(); indxRow <= iMaxRow; indxRow++) {

Row row=sheet.getRow(indxRow);if (row == null) {if(logger.isDebugEnabled()) {

logger.debug("Row is null,sheetIndex:" + sheetIndx + ",RowNo:" +indxRow);

}continue;

}

List rowContent = new ArrayList();//當最大列為0時 讀取最大CellNum

if (iMaxColumn == 0) {

iMaxColumn=row.getLastCellNum();

}boolean hasContent = false;for (int indxColumn = 0; indxColumn < iMaxColumn; indxColumn++) {

String cellValue= null;

Cell cell=row.getCell(indxColumn);if (cell == null) {if(logger.isDebugEnabled()) {

logger.debug("Cell is null,sheetIndex:" + sheetIndx + ",RowNo:" + indxRow + ",CellNo:" +indxColumn);

}

}else{

cellValue=getCellStrValue(cell);

}//如果 读到的内容不是空 代表这行有数据

if (cellValue != null && cellValue.length() > 0) {

hasContent= true;

}//不论当前格是否有数据都加入.

rowContent.add(cellValue);

}//这一行有内容 则加入

if(hasContent) {

resList.add(rowContent);

}

}returnresList;

}public staticString getCellStrValue(Cell cell) {

String res= "";try{

res=cell.getStringCellValue();

}catch(Exception e) {

DecimalFormat df= new DecimalFormat("#");

res= df.format(cell.getNumericCellValue()) + "";

}returnres;

}/***

* @Description: 将集合转换成字符串输出

*@paramcoll

*@return设定文件

*@throws异常说明

*@authoralbert.su suzy@malam.com

* @date 2014年5月7日 下午12:35:55*/

public static String collectionToCsvString(Collection>coll) {if(CollectionUtils.isEmpty(coll)) {return "";

}

StringBuilder sb= newStringBuilder();

Iterator> it =coll.iterator();while(it.hasNext()) {

Object object=it.next();if (String.valueOf(object).matches("[0-9,\\.]+") || String.valueOf(object).contains(",")) {

sb.append("\"\t");

sb.append(object);

sb.append("\"");

}else{

sb.append("\t");

sb.append(object);

}if(it.hasNext()) {

sb.append(",");

}

}returnsb.toString();

}//以下为糯米的代码, 建议少用

/*** 从InputStream读取Excel workbook

*

*@paramins

*@return*@throwsIOException

*@throwsFileNotFoundException*/

public static HSSFWorkbook readWorkbook(InputStream ins) throwsIOException, FileNotFoundException {

ByteArrayOutputStream byteOS= newByteArrayOutputStream();

BufferedInputStream bis= newBufferedInputStream(ins);byte[] by = new byte[512];int t = bis.read(by, 0, by.length);while (t > 0) {

byteOS.write(by,0, 512); //read 512

t = bis.read(by, 0, by.length);

}

byteOS.close();

InputStream byteIS= newByteArrayInputStream(byteOS.toByteArray());

HSSFWorkbook wbDest= newHSSFWorkbook(byteIS);returnwbDest;

}public static void writeToResponse(HttpServletResponse response, HSSFWorkbook wb, String fileName) throwsIOException {

response.setContentType("application/ms-download");

response.setCharacterEncoding("gb2312");

response.setHeader("Content-Disposition", "filename=" +fileName);

OutputStream out=response.getOutputStream();

wb.write(out);

out.flush();

out.close();

}/*** 判断单元格的格式

*

*@paramcell

* 单元格

*@returnString 将excel各种单元格的类型转换为String类型*/

public staticString getCellStringValue(HSSFCell cell) {//转换后单元格的值

String value = "";if (cell != null) {switch(cell.getCellType()) {caseHSSFCell.CELL_TYPE_STRING:

value=cell.getRichStringCellValue().getString();break;caseHSSFCell.CELL_TYPE_NUMERIC:if(HSSFDateUtil.isCellDateFormatted(cell)) {

Date date=HSSFDateUtil.getJavaDate(cell.getNumericCellValue());

SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");

value=sdf.format(date);

}else{

DecimalFormat formatter= new DecimalFormat("########");

value=formatter.format(cell.getNumericCellValue());

}break;caseHSSFCell.CELL_TYPE_FORMULA:

cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

value=String.valueOf(cell.getNumericCellValue());break;caseHSSFCell.CELL_TYPE_BLANK:break;caseHSSFCell.CELL_TYPE_BOOLEAN:break;caseHSSFCell.CELL_TYPE_ERROR:break;default:break;

}

}returnvalue;

}/*** 到出excel的Helper类

*

*@authorAdministrator

**/

public static classExcelRowBuilder {privateHSSFRow row;private short index = 0;publicExcelRowBuilder(HSSFRow row) {this.row =row;

}

@SuppressWarnings("deprecation")publicExcelRowBuilder addCell(String str) {

HSSFCell cell= row.createCell(index++);

cell.setCellValue(newHSSFRichTextString(str));return this;

}

@SuppressWarnings("deprecation")public ExcelRowBuilder addCell(longvalue) {

HSSFCell cell= row.createCell(index++);

cell.setCellValue(value);return this;

}

@SuppressWarnings("deprecation")public ExcelRowBuilder addCell(doublevalue) {

HSSFCell cell= row.createCell(index++);

cell.setCellValue(value);return this;

}

}

}