/***
*
* response.setContentType("application/binary;charset=UTF-8");
ExportListToExcelUtil exportUtil = new ExportListToExcelUtil();
List<Menu> list=service.getAllMenu();
String title="测试ExportListToExcelUtil";
String[] columnFile={"商品名", "商品单价","商品单位"};
ExportListToExcelUtil exportListToExcelUtil = new ExportListToExcelUtil();
exportListToExcelUtil.exportExcelmethod(response, title, columnFile, list, "com.zotion.apcs.model.Menu");
*/
package com.zotion.apcs.util;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.util.Iterator;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.json.JSONArray;
import org.json.JSONObject;
import com.alibaba.fastjson.JSON;
import com.zotion.apcs.pagemodel.GridView;
@SuppressWarnings("all")
public class ExcelUtil {
// 打印日志
private static Logger log = Logger.getLogger(ExcelUtil.class);
private Sheet sheet = null;
private Workbook workBook = null;
public ExcelUtil() {
}
public void exportExcelByList(HttpServletRequest request, String title, List list) {
workBook = new HSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
title = title.replaceAll(":", "").trim().substring(0, title.length() - 6);
sheet = workBook.createSheet(title);
CellStyle headStyle = getHeadStyle(workBook);
CellStyle bodyStyle = getBodyStyle(workBook);
// 构建表头
Row headRow = sheet.createRow(0);
Cell cell = null;
JSONArray jsonArray = new JSONArray(JSON.toJSONString(list));
// 构建表体数据
if (jsonArray.length() > 0) {
// 通过默认构造方法创建一个新的对象
JSONObject hjsonObj = jsonArray.getJSONObject(0);
Iterator hiter_d = hjsonObj.keys();
int h=0;
while (hiter_d.hasNext()) {
String key = hiter_d.next().toString();
cell = headRow.createCell(h);
cell.setCellStyle(headStyle);
cell.setCellValue(key);
h++;
}
for (int j = 0; j < jsonArray.length(); j++) {
Row bodyRow = sheet.createRow(j + 1);
JSONObject jsonObj = jsonArray.getJSONObject(j);
Iterator iter_d = jsonObj.keys();
while (iter_d.hasNext()) {
String key = iter_d.next().toString();
String value = jsonObj.get(key) + "";
for (int i = 0; i < h; i++) {
HSSFRow prehssfRow = null;
prehssfRow = (HSSFRow) sheet.getRow(0);
String daindex = ExcelUtil.formatCell(prehssfRow.getCell(i));
daindex=StringUtil.isNotEmpty(daindex)?daindex:"";
if (daindex.equals(key)) {
Object ssString = value;
cell = bodyRow.createCell(i);
cell.setCellStyle(bodyStyle);
String tmp = "";
if (ssString != null) {
tmp = ssString.toString();
}
//此处的double转换判断是否是数值类型的操作 请不要修改
try {
cell.setCellValue(Double.parseDouble(tmp));
} catch (Exception e) {
cell.setCellValue(tmp);
}
sheet.setColumnWidth(i, tmp.length() * 100 + 3000);
break;
}
}
}
}
}
try {
FileOutputStream fileOut = new FileOutputStream(pathAndFileName);// 创建文件流
workBook.write(fileOut);// 把Workbook对象输出到路径path中
} catch (IOException e) {
log.error(e.getMessage() + e.getStackTrace());
} finally {
try {
} catch (Exception e) {
log.error(e.getMessage() + e.getStackTrace());
}
}
} catch (Exception e) {
log.error(e.getMessage() + e.getStackTrace());
}
}
public String exportExcelmethod(HttpServletRequest request, String title, List<GridView> gridlist, List list) throws Exception {
String pathAndFileName = request.getSession().getServletContext().getRealPath("/upload/excel/") + "/" + title;
try {
workBook = new HSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
title = title.replaceAll(":", "").trim().substring(0, title.length() - 6);
sheet = workBook.createSheet(title);
CellStyle headStyle = getHeadStyle(workBook);
CellStyle bodyStyle = getBodyStyle(workBook);
// 构建表头
Row headRow = sheet.createRow(0);
Cell cell = null;
// 通过默认构造方法创建一个新的对象
for (int i = 0; i < gridlist.size(); i++) {
cell = headRow.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(gridlist.get(i).getText());
}
JSONArray jsonArray = new JSONArray(JSON.toJSONString(list));
// 构建表体数据
if (jsonArray.length() > 0) {
for (int j = 0; j < jsonArray.length(); j++) {
Row bodyRow = sheet.createRow(j + 1);
JSONObject jsonObj = jsonArray.getJSONObject(j);
Iterator iter_d = jsonObj.keys();
while (iter_d.hasNext()) {
String key = iter_d.next().toString();
String value = jsonObj.get(key) + "";
for (int i = 0; i < gridlist.size(); i++) {
String mmString = gridlist.get(i).getDataIndex();
if (gridlist.get(i).getDataIndex().equals(key)) {
Object ssString = value;
cell = bodyRow.createCell(i);
cell.setCellStyle(bodyStyle);
String tmp = "";
if (ssString != null) {
tmp = ssString.toString();
}
if (StringUtil.isNotEmpty(gridlist.get(i).getRenderer())) {
if ("true".equals(tmp)) {
tmp = "是";
} else if ("false".equals(tmp)) {
tmp = "否";
}
if ("1".equals(tmp)) {
tmp = "是";
} else if ("0".equals(tmp)) {
tmp = "否";
}
}
//此处的double转换判断是否是数值类型的操作 请不要修改
try {
cell.setCellValue(Double.parseDouble(tmp));
} catch (Exception e) {
cell.setCellValue(tmp);
}
sheet.setColumnWidth(i, tmp.length() * 100 + 3000);
break;
}
}
}
}
}
try {
FileOutputStream fileOut = new FileOutputStream(pathAndFileName);// 创建文件流
workBook.write(fileOut);// 把Workbook对象输出到路径path中
} catch (IOException e) {
log.error(e.getMessage() + e.getStackTrace());
} finally {
try {
} catch (Exception e) {
log.error(e.getMessage() + e.getStackTrace());
}
}
} catch (Exception e) {
log.error(e.getMessage() + e.getStackTrace());
} finally {
}
return null;
}
/**
* 设置表头的单元格样式
*
* @return
*/
public CellStyle getHeadStyle(Workbook workBook) {
// 创建单元格样式
CellStyle cellStyle = workBook.createCellStyle();
// 设置单元格的背景颜色为淡蓝色
cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
// cellStyle.setWrapText(true);
// 设置单元格字体样式
Font font = workBook.createFont();
// 设置字体加粗
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("黑体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
return cellStyle;
}
/**
* 设置表体的单元格样式
*
* @return
* @throws IOException
*/
public CellStyle getBodyStyle(Workbook workBook) {
// 创建单元格样式
CellStyle cellStyle = workBook.createCellStyle();
// 设置单元格居中对齐
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
Font font = workBook.createFont();
// 设置字体加粗
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
return cellStyle;
}
public static String formatCell(HSSFCell hssfCell) {
if (hssfCell == null) {
return "";
} else {
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());
}
}
}
}
*
* response.setContentType("application/binary;charset=UTF-8");
ExportListToExcelUtil exportUtil = new ExportListToExcelUtil();
List<Menu> list=service.getAllMenu();
String title="测试ExportListToExcelUtil";
String[] columnFile={"商品名", "商品单价","商品单位"};
ExportListToExcelUtil exportListToExcelUtil = new ExportListToExcelUtil();
exportListToExcelUtil.exportExcelmethod(response, title, columnFile, list, "com.zotion.apcs.model.Menu");
*/
package com.zotion.apcs.util;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.util.Iterator;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.json.JSONArray;
import org.json.JSONObject;
import com.alibaba.fastjson.JSON;
import com.zotion.apcs.pagemodel.GridView;
@SuppressWarnings("all")
public class ExcelUtil {
// 打印日志
private static Logger log = Logger.getLogger(ExcelUtil.class);
private Sheet sheet = null;
private Workbook workBook = null;
public ExcelUtil() {
}
public void exportExcelByList(HttpServletRequest request, String title, List list) {
try {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
String currTime = df.format(new Date());// new Date()为获取当前系统时间
title= title+currTime.replaceAll(":", ":").replaceAll(" ", " ")+".xls";
workBook = new HSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
title = title.replaceAll(":", "").trim().substring(0, title.length() - 6);
sheet = workBook.createSheet(title);
CellStyle headStyle = getHeadStyle(workBook);
CellStyle bodyStyle = getBodyStyle(workBook);
// 构建表头
Row headRow = sheet.createRow(0);
Cell cell = null;
JSONArray jsonArray = new JSONArray(JSON.toJSONString(list));
// 构建表体数据
if (jsonArray.length() > 0) {
// 通过默认构造方法创建一个新的对象
JSONObject hjsonObj = jsonArray.getJSONObject(0);
Iterator hiter_d = hjsonObj.keys();
int h=0;
while (hiter_d.hasNext()) {
String key = hiter_d.next().toString();
cell = headRow.createCell(h);
cell.setCellStyle(headStyle);
cell.setCellValue(key);
h++;
}
for (int j = 0; j < jsonArray.length(); j++) {
Row bodyRow = sheet.createRow(j + 1);
JSONObject jsonObj = jsonArray.getJSONObject(j);
Iterator iter_d = jsonObj.keys();
while (iter_d.hasNext()) {
String key = iter_d.next().toString();
String value = jsonObj.get(key) + "";
for (int i = 0; i < h; i++) {
HSSFRow prehssfRow = null;
prehssfRow = (HSSFRow) sheet.getRow(0);
String daindex = ExcelUtil.formatCell(prehssfRow.getCell(i));
daindex=StringUtil.isNotEmpty(daindex)?daindex:"";
if (daindex.equals(key)) {
Object ssString = value;
cell = bodyRow.createCell(i);
cell.setCellStyle(bodyStyle);
String tmp = "";
if (ssString != null) {
tmp = ssString.toString();
}
//此处的double转换判断是否是数值类型的操作 请不要修改
try {
cell.setCellValue(Double.parseDouble(tmp));
} catch (Exception e) {
cell.setCellValue(tmp);
}
sheet.setColumnWidth(i, tmp.length() * 100 + 3000);
break;
}
}
}
}
}
try {
FileOutputStream fileOut = new FileOutputStream(pathAndFileName);// 创建文件流
workBook.write(fileOut);// 把Workbook对象输出到路径path中
} catch (IOException e) {
log.error(e.getMessage() + e.getStackTrace());
} finally {
try {
} catch (Exception e) {
log.error(e.getMessage() + e.getStackTrace());
}
}
} catch (Exception e) {
log.error(e.getMessage() + e.getStackTrace());
}
}
public String exportExcelmethod(HttpServletRequest request, String title, List<GridView> gridlist, List list) throws Exception {
String pathAndFileName = request.getSession().getServletContext().getRealPath("/upload/excel/") + "/" + title;
try {
workBook = new HSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
title = title.replaceAll(":", "").trim().substring(0, title.length() - 6);
sheet = workBook.createSheet(title);
CellStyle headStyle = getHeadStyle(workBook);
CellStyle bodyStyle = getBodyStyle(workBook);
// 构建表头
Row headRow = sheet.createRow(0);
Cell cell = null;
// 通过默认构造方法创建一个新的对象
for (int i = 0; i < gridlist.size(); i++) {
cell = headRow.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(gridlist.get(i).getText());
}
JSONArray jsonArray = new JSONArray(JSON.toJSONString(list));
// 构建表体数据
if (jsonArray.length() > 0) {
for (int j = 0; j < jsonArray.length(); j++) {
Row bodyRow = sheet.createRow(j + 1);
JSONObject jsonObj = jsonArray.getJSONObject(j);
Iterator iter_d = jsonObj.keys();
while (iter_d.hasNext()) {
String key = iter_d.next().toString();
String value = jsonObj.get(key) + "";
for (int i = 0; i < gridlist.size(); i++) {
String mmString = gridlist.get(i).getDataIndex();
if (gridlist.get(i).getDataIndex().equals(key)) {
Object ssString = value;
cell = bodyRow.createCell(i);
cell.setCellStyle(bodyStyle);
String tmp = "";
if (ssString != null) {
tmp = ssString.toString();
}
if (StringUtil.isNotEmpty(gridlist.get(i).getRenderer())) {
if ("true".equals(tmp)) {
tmp = "是";
} else if ("false".equals(tmp)) {
tmp = "否";
}
if ("1".equals(tmp)) {
tmp = "是";
} else if ("0".equals(tmp)) {
tmp = "否";
}
}
//此处的double转换判断是否是数值类型的操作 请不要修改
try {
cell.setCellValue(Double.parseDouble(tmp));
} catch (Exception e) {
cell.setCellValue(tmp);
}
sheet.setColumnWidth(i, tmp.length() * 100 + 3000);
break;
}
}
}
}
}
try {
FileOutputStream fileOut = new FileOutputStream(pathAndFileName);// 创建文件流
workBook.write(fileOut);// 把Workbook对象输出到路径path中
} catch (IOException e) {
log.error(e.getMessage() + e.getStackTrace());
} finally {
try {
} catch (Exception e) {
log.error(e.getMessage() + e.getStackTrace());
}
}
} catch (Exception e) {
log.error(e.getMessage() + e.getStackTrace());
} finally {
}
return null;
}
/**
* 设置表头的单元格样式
*
* @return
*/
public CellStyle getHeadStyle(Workbook workBook) {
// 创建单元格样式
CellStyle cellStyle = workBook.createCellStyle();
// 设置单元格的背景颜色为淡蓝色
cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
// cellStyle.setWrapText(true);
// 设置单元格字体样式
Font font = workBook.createFont();
// 设置字体加粗
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("黑体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
return cellStyle;
}
/**
* 设置表体的单元格样式
*
* @return
* @throws IOException
*/
public CellStyle getBodyStyle(Workbook workBook) {
// 创建单元格样式
CellStyle cellStyle = workBook.createCellStyle();
// 设置单元格居中对齐
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
Font font = workBook.createFont();
// 设置字体加粗
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
return cellStyle;
}
public static String formatCell(HSSFCell hssfCell) {
if (hssfCell == null) {
return "";
} else {
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());
}
}
}
}