1.为了方便excel导出方便,说先我在项目中创建了一个文件夹,用来存放所有的excel模板,当然了根据你自己的需要添加模板,如下图:
2.然后进行导出操作,我这里用的还是spring mvc,进行求导出操作,代码如下:
@RequestMapping(value = "/exportOrderReport")
@ResponseBody
public ResultVo exportOrderReport(HttpServletRequest request,
HttpServletResponse response, String orderNo, String productName,
String groupName, String groupLeaderName, String userWxNickname,
String orderStatus,String startTime,String endTime,String receiverName,String receiverPhoneNo) throws Exception
{
// 获取excel模板文件
String reportTemplatePath = Constants.PROJECT_ROOT_PATH
+ "/reportTemplate/order_report.xls";
// 创建工作簿
HSSFWorkbook workBook = POIUtil.getWorkbook(reportTemplatePath);
HSSFSheet sheet = workBook.getSheetAt(0);
// 获取数据list
OrderVo orderVo = new OrderVo();
orderVo.setOrderNo(orderNo);
orderVo.setProductName(productName);
orderVo.setGroupName(groupName);
orderVo.setGroupLeaderName(groupLeaderName);
orderVo.setUserWxNickname(userWxNickname);
orderVo.setOrderStatus(orderStatus);
orderVo.setStartTime(startTime);
orderVo.setEndTime(endTime);
orderVo.setReceiverName(receiverName);
orderVo.setReceiverPhoneNo(receiverPhoneNo);
List<OrderVo> orderList = orderService.selectOrderList(orderVo);
if (orderList != null && orderList.size() > 0)
{
//循环list往模板中添加数据
HSSFCell cell = null;
HSSFRow row = null;
for (int i = 0; i < orderList.size(); i++)
{
OrderVo vo = orderList.get(i);
int index = i + 2;
row = sheet.createRow(index);
cell = row.createCell(0);
cell.setCellValue(i + 1);
cell = row.createCell(1);
cell.setCellValue(vo.getOrderNo());
cell = row.createCell(2);
cell.setCellValue(vo.getOrderStatusName());
cell = row.createCell(3);
cell.setCellValue(vo.getTradeTime());
cell = row.createCell(4);
cell.setCellValue(vo.getTotalPrice());
cell = row.createCell(5);
cell.setCellValue(vo.getProductName());
cell = row.createCell(6);
cell.setCellValue(vo.getTradeCount());
cell = row.createCell(7);
cell.setCellValue(vo.getTradePrice());
cell = row.createCell(8);
cell.setCellValue(vo.getSupplierName());
cell = row.createCell(9);
cell.setCellValue(vo.getReceiverName());
cell = row.createCell(10);
cell.setCellValue(vo.getReceiverPhoneNo());
cell = row.createCell(11);
cell.setCellValue(vo.getReceiverAddress());
cell = row.createCell(12);
cell.setCellValue(vo.getPostCode());
cell = row.createCell(13);
cell.setCellValue(vo.getOrderRemark());
cell = row.createCell(14);
cell.setCellValue(vo.getUserWxNickname());
cell = row.createCell(15);
cell.setCellValue(vo.getGroupLeaderName());
cell = row.createCell(16);
cell.setCellValue(vo.getBaseBonus());
cell = row.createCell(17);
cell.setCellValue(vo.getHighBonus());
}
}
// 导出excel
POIUtil.doExportExcel(response, workBook, "订单报表");
return null;
}
3.POIUtil.java是个工具类,主要包含对excel的各种操作,比如创建工作簿,移除sheet,获取cell等等,具体参考下面代码:
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.imageio.ImageIO;
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.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class POIUtil
{
/**
* LATTER_ASSCII
*/
private static final int LATTER_ASSCII = 64;
/**
* LATTER_COUNT
*/
private static final int LATTER_COUNT = 26;
/**
* NUMBER_255
*/
private static final int NUMBER_255 = 255;
/**
* NUMBER_6
*/
private static final int NUMBER_6 = 6;
/**
* DT_SF(时间格式字符串)
*/
private static final String DT_SF = "yyyy-MM-dd";
/**
* 创建工作薄
*
* @param tmpNm
* @return
* @throws Exception
*/
public static HSSFWorkbook getWorkbook(String tmpNm) throws Exception
{
FileInputStream fis = new FileInputStream(tmpNm);
HSSFWorkbook wb = new HSSFWorkbook(fis);
fis.close();
return wb;
}
/**
* 创建工作薄
*
* @param tmpNm
* @return
* @throws Exception
*/
public static HSSFWorkbook getWorkbook(File file) throws Exception
{
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook workbook = new HSSFWorkbook(fs);
return workbook;
}
/**
* 导出Excel
*
* @param response
* @param wb
* @param excelName
* @return
* @throws Exception
*/
public static boolean doExportExcel(HttpServletResponse response,
Workbook wb, String excelName) throws Exception
{
SimpleDateFormat sf = new SimpleDateFormat(DT_SF);
String ctime = sf.format(new Date()).toString();// 取时间字符串
String fileName = excelName + ctime + ".xls";// 文件名
OutputStream out = response.getOutputStream();
try
{
response.setHeader("Content-disposition", "attachment;filename="
+ URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("application/msexcel;charset=UTF-8");
wb.write(out);
return true;
} finally
{
out.close();
}
}
/**
* 移除Sheet
*
* @param wb
* @param name
*/
public static void removeSheet(HSSFWorkbook wb, String name)
{
int index = wb.getSheetIndex(name);
wb.removeSheetAt(index);
}
/**
* 获取Cell
*
* @param sheet
* @param rowIndex
* @param columnIndex
* @return
*/
public static HSSFCell getCell(HSSFSheet sheet, int rowIndex,
int columnIndex)
{
HSSFCell cell = null;
if (rowIndex < 0 || columnIndex < 0)
{
return cell;
}
HSSFRow row = sheet.getRow(rowIndex);
if (row != null)
{
cell = row.getCell(columnIndex);
}
else
{
row = sheet.createRow(columnIndex);
}
if (cell == null)
{
cell = row.createCell(columnIndex);
}
return cell;
}
/**
* 获取Cell
*
* @param sheet
* @param rowIndex
* @param columnName
* @return
*/
public static HSSFCell getCell(HSSFSheet sheet, int rowIndex,
String columnName)
{
int columnIndex = POIUtil.getColumnIndex(columnName);
return POIUtil.getCell(sheet, rowIndex, columnIndex);
}
/**
* 获取Cell
*
* @param sheet
* @param cellName
* @return
*/
public static HSSFCell getCell(HSSFSheet sheet, String cellName)
{
Pattern pattern = Pattern.compile("^([A-Z]*)([0-9]*)");
Matcher matcher = pattern.matcher(cellName);
if (!matcher.find())
{
return null;
}
String columnName = matcher.group(1);
int columnIndex = POIUtil.getColumnIndex(columnName);
int rowIndex = Integer.parseInt(matcher.group(2));
return POIUtil.getCell(sheet, rowIndex - 1, columnIndex);
}
/**
* 获取Cell
*
* @param row
* @param columnName
* @return
*/
public static HSSFCell getCell(HSSFRow row, String columnName)
{
int columnIndex = POIUtil.getColumnIndex(columnName);
return row.getCell(columnIndex);
}
/**
* 读取不同格式的值
*
* @param cell
* @return
*/
public static String readXls(Cell cell)
{
if (cell != null)
{
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
double d = cell.getNumericCellValue();
int i = (int) d;
return String.valueOf(i).toString().trim();
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue().toString().trim();
case Cell.CELL_TYPE_BOOLEAN:
return String.valueOf(cell.getBooleanCellValue()).toString()
.trim();
case Cell.CELL_TYPE_FORMULA:
return String.valueOf(cell.getNumericCellValue()).toString()
.trim();
case Cell.CELL_TYPE_BLANK:
return "";
default:
// 类型不匹配 返回空
return "";
}
}
else
{
return "";
}
}
/**
* 获取列索引
*
* @param columnName
* @return
*/
public static int getColumnIndex(String columnName)
{
int index = 0;
if (StringUtils.isBlank(columnName))
{
return index;
}
int len = columnName.length();
for (int i = 0; i < len; i++)
{
int num = ((int) columnName.toUpperCase().charAt(len - 1 - i))
- LATTER_ASSCII;
if (i == 0)
{
index = num;
continue;
}
index = LATTER_COUNT * i * num + index;
}
return --index;
}
/**
* 插入行(行之前)
*
* @param sheet
* @param rowIndex
* @param tr
* @return
*/
public static HSSFRow insertRowBefore(HSSFSheet sheet, int rowIndex,
HSSFRow tr)
{
sheet.shiftRows(rowIndex, sheet.getLastRowNum(), 1, true, false);
HSSFRow row = sheet.createRow(rowIndex);
initRowAsTemplate(row, tr);
return row;
}
/**
* 插入行(行之后)
*
* @param sheet
* @param rowIndex
* @param tr
* @return
*/
public static HSSFRow insertRowAfter(HSSFSheet sheet, int rowIndex,
HSSFRow tr)
{
return insertRowBefore(sheet, rowIndex + 1, tr);
}
/**
* 插入行(行之后)
*
* @param sheet
* @param rowIndex
* @param tr
* @return
*/
public static void insertRowAfter(HSSFSheet sheet, int rowIndex,
HSSFRow tr, int count)
{
for (int i = 0; i < count; i++)
{
insertRowBefore(sheet, rowIndex + i + 1, tr);
}
}
/**
* 插入行(模板行)
*
* @param row
* @param tr
*/
public static void initRowAsTemplate(HSSFRow row, HSSFRow tr)
{
HSSFSheet sheet = row.getSheet();
row.setHeight(tr.getHeight());
// set style of inserted cell
Iterator<Cell> templateCells = tr.cellIterator();
while (templateCells.hasNext())
{
Cell tc = templateCells.next();
CellStyle cellStyle = tc.getCellStyle();
Integer cellType = tc.getCellType();
Cell cell = row.createCell(tc.getColumnIndex());
if (cellStyle != null)
{
cell.setCellStyle(cellStyle);
}
if (cellType != null)
{
cell.setCellType(cellType);
}
}
// merged cell of inserted row
List<CellRangeAddress> mergedRegionList = POIUtil.getMergedRegions(
tr.getSheet(), tr.getRowNum());
for (CellRangeAddress cra : mergedRegionList)
{
CellRangeAddress mr = new CellRangeAddress(row.getRowNum(),
row.getRowNum(), cra.getFirstColumn(), cra.getLastColumn());
sheet.addMergedRegion(mr);
}
}
public static List<CellRangeAddress> getMergedRegions(HSSFSheet sheet,
int rowIndex)
{
List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++)
{
CellRangeAddress cra = sheet.getMergedRegion(i);
int firstRow = cra.getFirstRow();
if (firstRow == (rowIndex))
{
list.add(cra);
}
}
return list;
}
public static void merge(HSSFSheet sheet, int ltRowIndex,
int ltColumnIndex, int rbRowIndex, int rbColumnIndex)
{
CellRangeAddress mr = new CellRangeAddress(ltRowIndex, rbRowIndex,
ltColumnIndex, rbColumnIndex);
sheet.addMergedRegion(mr);
}
public static void merge(HSSFCell ltCell, HSSFCell rbCell)
{
if (ltCell != null && rbCell != null)
{
merge(ltCell.getSheet(), ltCell.getRowIndex(),
ltCell.getColumnIndex(), rbCell.getRowIndex(),
rbCell.getColumnIndex());
}
}
public static void merge(HSSFSheet sheet, String ltCellName,
String rbCellName)
{
HSSFCell ltCell = POIUtil.getCell(sheet, ltCellName);
HSSFCell rbCell = POIUtil.getCell(sheet, rbCellName);
POIUtil.merge(ltCell, rbCell);
}
public static void mergeRow(HSSFSheet sheet, int rowStartIndex,
int rowEndIndex, String columnName)
{
int columnIndex = POIUtil.getColumnIndex(columnName);
merge(sheet, rowStartIndex, columnIndex, rowEndIndex, columnIndex);
}
public static void lock(HSSFCell cell, boolean locked)
{
HSSFWorkbook wb = cell.getSheet().getWorkbook();
HSSFCellStyle style = wb.createCellStyle();
style.cloneStyleFrom(cell.getCellStyle());
style.setLocked(locked);
cell.setCellStyle(style);
}
/**
* 导出图片(Excel中)
*
* @param wb
* @param patriarch
* @param imgName
* @param col1
* @param row1
* @param col2
* @param row2
* @throws IOException
*/
public static void printPicture(HSSFWorkbook wb, HSSFPatriarch patriarch,
String imgName, int col1, int row1, int col2, int row2)
throws IOException
{
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
try
{
if (imgName != null)
{
BufferedImage bufferImg = ImageIO.read(new File(imgName));
ImageIO.write(bufferImg, "jpg", byteArrayOut);
}
}
catch(IOException e)
{
throw new IOException(e.getMessage(), e);
}
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, NUMBER_255,
NUMBER_255, (short) col1, row1, (short) col2, row2);
anchor.setAnchorType(NUMBER_6);
patriarch.createPicture(anchor, wb.addPicture(
byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
}
至此,代码结束,具体可以看代码中的注释。