import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 报表设计思想是： 1、先设计Excel模板 2、通过文件名来找到对应的模板 3、子类实现填入对应数据
* ClassName:ExcelTemplateReport
*
* Function: TODO ADD FUNCTION Reason: TODO ADD REASON
*
* @author springmvc2006@sina.com
* @version
* @since Ver 1.0.1
* @Date 2012-10-31
*/
public class CopyOfExcelTemplateReport {
protected Logger logger = LoggerFactory.getLogger(getClass());
/**
* replaceExcelCell(主要是替换标记) (这里描述这个方法适用条件 – 可选)
*
* @param inputStream
* @param sheetName
* @param map
* @throws Exception
* @exception
* @since 1.0.1
*/
public void replaceExcelCell(InputStream inputStream, String sheetName,
Map<String, Object> map, OutputStream outputStream)
throws Exception {
HSSFWorkbook workbook = null;
HSSFSheet worksheet = null;
try {
workbook = new HSSFWorkbook(inputStream);
worksheet = workbook.getSheet(sheetName);
HSSFRow hssfRow = null;
HSSFCell hssfCell = null;
String cellValue = null;
String tmpValue = null;
for (int i = 0, size = worksheet.getPhysicalNumberOfRows(); i < size; i++) {
hssfRow = worksheet.getRow(i);
if (hssfRow != null) {
for (int j = 0, len = hssfRow.getLastCellNum(); j < len; j++) {
hssfCell = hssfRow.getCell(j);
cellValue = hssfCell.getStringCellValue();
if (StringUtils.isNotEmpty(cellValue)) {
if (cellValue.indexOf("#data_") > -1) {
tmpValue = (String) map.get(cellValue.trim());
if (StringUtils.isNotEmpty(tmpValue)) {
hssfCell.setCellValue(tmpValue);
} else {
hssfCell.setCellValue("");
}
}
}
}
}
}
workbook.write(outputStream);
outputStream.flush();
} catch (Exception e) {
throw e;
} finally {
if (inputStream != null) {
try {
inputStream.close();
inputStream = null;
} catch (IOException e) {
}
}
}
}
/**
* mergedRegion(合并单元格) (这里描述这个方法适用条件 – 可选)
*
* @param inputStream
* @param sheetName
* @param mergedParams
* void
* @exception
* @since 1.0.1
*/
public void mergedRegion(InputStream inputStream, String sheetName,
List<MergedParam> mergedParams, OutputStream outputStream)
throws Exception {
HSSFWorkbook workbook = null;
HSSFSheet worksheet = null;
try {
workbook = new HSSFWorkbook(inputStream);
worksheet = workbook.getSheet(sheetName);
if (mergedParams != null && mergedParams.size() > 0) {
MergedParam mergedParam = null;
CellRangeAddress cellRangeAddress = null;
int startRow = -1;
int endRow = -1;
int startCol = -1;
int endCol = -1;
HSSFCellStyle cloneStyle = null;
HSSFCellStyle tmpStyle = null;
HSSFRow row_temp = null;
HSSFFont font = null;
for (int k = 0, size = mergedParams.size(); k < size; k++) {
mergedParam = mergedParams.get(k);
if (mergedParam == null) {
continue;
}
startRow = mergedParam.getStartRow();
endRow = mergedParam.getEndRow();
startCol = mergedParam.getStartCol();
endCol = mergedParam.getEndCol();
cellRangeAddress = new CellRangeAddress(startRow, endRow,
startCol, endCol);
worksheet.addMergedRegion(cellRangeAddress);
// 设置样式
cloneStyle = mergedParam.getStyle();
for (int i = startRow; i <= endRow; i++) {
row_temp = getRow(worksheet, i);
for (int j = startCol; j <= endCol; j++) {
HSSFCell cell_temp = row_temp.getCell(j);
if (cell_temp == null) {
cell_temp = row_temp.createCell(j);
}
cloneStyle = cell_temp.getCellStyle();
if (cloneStyle != null) {
cloneStyle = workbook.createCellStyle();
}
tmpStyle = cell_temp.getCellStyle();
cloneStyle.cloneStyleFrom(tmpStyle);
cloneStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cloneStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cloneStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cloneStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cloneStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
cloneStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
// 单元格字体
font = workbook.createFont();
if (mergedParam.isBold()) {
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}else{
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
}
cloneStyle.setFont(font);
cell_temp.setCellStyle(cloneStyle);
}
}
}
}
workbook.write(outputStream);
outputStream.flush();
} catch (Exception e) {
throw e;
} finally {
if (inputStream != null) {
inputStream.close();
inputStream = null;
}
}
}
/**
* addRow(这里用一句话描述这个方法的作用)
* (这里描述这个方法适用条件 – 可选)
* @param startRow
* @param startCol
* @param dataList
*void
* @exception
* @since 1.0.1
*/
public void addRows(InputStream inputStream, String sheetName, int startRow, int startCol, List<List<String>> dataList, OutputStream outputStream)throws Exception {
HSSFWorkbook workbook = null;
HSSFSheet worksheet = null;
try {
workbook = new HSSFWorkbook(inputStream);
worksheet = workbook.getSheet(sheetName);
if (dataList != null && dataList.size() > 0) {
HSSFRow row = null;
HSSFCell cell = null;
List<String> tmpList = null;
String cellString = null;
HSSFCellStyle cellStyle = worksheet.getWorkbook().createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeight((short)200);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setFont(font);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
for(int i=startRow, size = startRow + dataList.size(); i < size; i++){
row =getRow(worksheet, i);
tmpList = dataList.get(i-startRow);
if (tmpList != null && tmpList.size() > 0) {
for (int j = startCol, len = startCol+tmpList.size(); j < len; j++) {
cell = getCell(row, j);
if (cellStyle != null) {
cellStyle.setWrapText(true);
cell.setCellStyle(cellStyle);
}
cellString = tmpList.get(j-startCol);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(new HSSFRichTextString(cellString));
}
}
}
}
workbook.write(outputStream);
outputStream.flush();
}catch (Exception e) {
throw e;
} finally {
if (inputStream != null) {
inputStream.close();
inputStream = null;
}
}
}
/**
* getRow(这里用一句话描述这个方法的作用)
* (这里描述这个方法适用条件 – 可选)
* @param workbook
* @param worksheet
* @param rowIndex
* @return HSSFRow
* @exception
* @since 1.0.1
*/
private HSSFRow getRow(HSSFSheet worksheet,
int rowIndex) {
HSSFRow row = worksheet.getRow(rowIndex);
if (row == null) {
row = worksheet.createRow(rowIndex);
row.setHeight((short) (300));
}
return row;
}
/**
* getRow(这里用一句话描述这个方法的作用)
* (这里描述这个方法适用条件 – 可选)
* @param workbook
* @param worksheet
* @param rowIndex
* @return HSSFRow
* @exception
* @since 1.0.1
*/
private HSSFCell getCell(HSSFRow row, int cellIndex) {
HSSFCell cell = row.getCell(cellIndex);
if (cell == null) {
cell = row.createCell(cellIndex);
}
return cell;
}
/**
* writeOutput(这里用一句话描述这个方法的作用) (这里描述这个方法适用条件 – 可选)
*
* @param inputStream
* @param outputStream
* void
* @exception
* @since 1.0.1
*/
public void writeOutput(InputStream inputStream, OutputStream outputStream)
throws Exception {
HSSFWorkbook workbook = null;
HSSFSheet worksheet = null;
try {
workbook = new HSSFWorkbook(inputStream);
workbook.write(outputStream);
} catch (Exception e) {
throw e;
} finally {
if (inputStream != null) {
inputStream.close();
inputStream = null;
}
if (outputStream != null) {
outputStream.close();
outputStream = null;
}
}
}
/**
* writeOutputEx(这里用一句话描述这个方法的作用)
* (这里描述这个方法适用条件 – 可选)
* @param inputStream
* @param fileName
* @param request
* @param response
* @throws Exception
*void
* @exception
* @since 1.0.1
*/
public void writeOutputEx(InputStream inputStream, String fileName, HttpServletRequest request, HttpServletResponse response)
throws Exception {
HSSFWorkbook workbook = null;
HSSFSheet worksheet = null;
try {
workbook = new HSSFWorkbook(inputStream);
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/x-msdownload ");
if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0){
try {
fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");
} catch (Exception e) {
}
}else{
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
} catch (UnsupportedEncodingException e1) {
}
}
response.setHeader("Content-Disposition", "attachment;fileName="+fileName);
OutputStream os = null;
try {
os = response.getOutputStream();
workbook.write(os);
os.flush();
} catch (Exception e) {
logger.error(e.toString());
}finally{
try {
os.close();
} catch (IOException e) {
logger.error(e.toString());
}
os = null;
}
} catch (Exception e) {
throw e;
} finally {
if (inputStream != null) {
inputStream.close();
inputStream = null;
}
}
}
public static void main(String[] args) throws Exception {
CopyOfExcelTemplateReport abstractReport = new CopyOfExcelTemplateReport();
Map map = new HashMap();
map.put("#data_value1", "事业");
ByteArrayOutputStream arrayOutputStream = new ByteArrayOutputStream();
abstractReport.replaceExcelCell(new FileInputStream(
"D:/log/人员信息统计报表.xls"), "人员信息统计报表", map, arrayOutputStream);
//合并
ByteArrayInputStream arrayInputStream = new ByteArrayInputStream(
arrayOutputStream.toByteArray());
List<MergedParam> mergedParams = new ArrayList<MergedParam>();
HSSFCellStyle cloneStyle = null;
mergedParams.add(new MergedParam(20, 0, 39, 0, true));
arrayOutputStream = new ByteArrayOutputStream();
abstractReport.mergedRegion(arrayInputStream, "人员信息统计报表", mergedParams,
arrayOutputStream);
//加入列表
arrayInputStream = new ByteArrayInputStream(
arrayOutputStream.toByteArray());
List<String> tmpStringList = null;
List<List<String>> listData = new ArrayList<List<String>>();
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("ssss1sss");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("ssss1sss");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("ssss1sss");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("ssss1sss");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
tmpStringList = new ArrayList<String>();
tmpStringList.add("1");
tmpStringList.add("2");
tmpStringList.add("3");
tmpStringList.add("4");
tmpStringList.add("5");
listData.add(tmpStringList);
arrayOutputStream = new ByteArrayOutputStream();
abstractReport.addRows(arrayInputStream, "人员信息统计报表", 24, 1, listData, arrayOutputStream);
arrayInputStream = new ByteArrayInputStream(arrayOutputStream.toByteArray());
OutputStream outputStream = new FileOutputStream(new File("D:/log/人员信息统计报表1.xls"));
abstractReport.writeOutput(arrayInputStream, outputStream);
}
}
扫一扫
53万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
