导出excel表格并合并单元格（不按模板导出）
java代码：
/**
* 下载清单
* @param response
* @param purchaseListQueryVo
* @param request
*/
@RequestMapping(value = "/downloadList", method = RequestMethod.POST)
public ResponseEntity<byte[]> downloadList(HttpServletResponse response, PurchaseListQueryVo purchaseListQueryVo, HttpServletRequest request) {
ResponseEntity<byte[]> responseEntity = null;
//获取前台额外传递过来的查询条件
if(log.isDebugEnabled()){
log.debug("purchaseListQueryVo:{}",purchaseListQueryVo);
}
try {
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
List<Map<String, Object>> postCheckList= purchaseListService.downloadList(getMap(purchaseListQueryVo));
String fileName = de("清单导出.xlsx", "UTF-8");
//⽣成EXCEL XLSX格式
//设置响应头让浏览器正确显⽰下载
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentDispositionFormData("attachment", fileName);
responseEntity = new ResponseEntity<>(ByteArray(), headers, HttpStatus.OK);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return responseEntity;
}
public void exportData(List<Map<String, Object>> postCheckList, OutputStream outputStream,PurchaseListQueryVo purchaseListQueryVo) {
String orderStartTime = OrderStartTime().replace("-", "/").substring(0,10);
String orderEndTime = OrderEndTime().replace("-", "/").substring(0,10);
String header = null;
if(Lang.OrderStartTime())||Lang.OrderEndTime())){
header = " 购货清单";
}else{
header = orderStartTime + "-"+ orderEndTime + " 购货清单";
}
//EXCEL标题2018-04-26
List<String> titles = Arrays.asList(new String[]{"序号","太平订单号/礼包账单号", "总公司", "采购员/礼包创建员机构", "部门", "采购员/礼包创建员", "采购员/礼包创建员账号","下单时间/账单时间", "⽀
付⽅式","应收凭证号","商品名称","订购数量","单价","商品总价","不含税⾦额","税额"});
//EXCEL列
List<List<Object>> rows = new ArrayList<>();
//从给定数据获取指定列作为EXCEL列数据
for (Map<String, Object> map: postCheckList) {
List<Object> row = new ArrayList<>();
//序号
row.("ROWNUM"));
//太平订单号
row.("ORDERNO"));
//总公司
row.("PARENTCOMPANY"));
//采购员机构
row.("INVOICENAME"));
//部门
row.("DEPARTMENT"));
//采购员
row.("REALNAME"));
//采购员账号
row.("USERNAME"));
//下单时间
row.("DATECREATED"));
/
/⽀付⽅式
row.("PAYNAME"));
//应收凭证号
row.("VOUCHERNO"));
//商品名称
row.("PRODUCTNAME"));
//订购数量
row.("COUNT"));
//单价
row.add(new BigDecimal(String.("SALEPRICE"))).setScale(2,BigDecimal.ROUND_HALF_UP).toPlainString());
//商品总价
row.add(new BigDecimal(String.("COMMODITYAMOUNT"))).setScale(2,BigDecimal.ROUND_HALF_UP).toPlainString());
//不含税⾦额
row.add(new BigDecimal(String.("NOTAXAMOUNT"))).setScale(2,BigDecimal.ROUND_HALF_UP).toPlainString());
//税额
row.add(new BigDecimal(String.("TAXAMOUNT"))).setScale(2,BigDecimal.ROUND_HALF_UP).toPlainString());
rows.add(row);
}
XSSFWorkbook xwb = excelService.purchaseListExcelForXLSX(header,titles, rows, "清单");
try {
xwb.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
xwb.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 专业公司购货清单⽣成Excel对象
* @param titles 表头
* @param rows 数据⾏
* @param sheetName ⼯作表名
* @return
*/
public XSSFWorkbook purchaseListExcelForXLSX(String header,List<String> titles, List<List<Object>> rows, String sheetName) { XSSFWorkbook xwb = new XSSFWorkbook();
XSSFSheet sheet = ateSheet(Lang.isEmpty(sheetName) ? "sheet1" : sheetName);
//创建第⼀⾏头部并设置⾏⾼及样式
sheet.addMergedRegion(new CellRangeAddress(0,0,0,titles.size()-1));
XSSFRow row1 = ateRow(0);
row1.setHeight((short)600);
//标题头部样式
//设置字体
XSSFFont fontHeader = ateFont();
fontHeader.setFontName("宋体");
fontHeader.setFontHeightInPoints((short)16);
fontHeader.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
XSSFDataFormat formatHeader = ateDataFormat();
/
/设置单元格格式
XSSFCellStyle styleHeader = ateCellStyle();
styleHeader.setFont(fontHeader);
styleHeader.Format("@")); //设置输⼊格式为⽂本格式
styleHeader.setAlignment(XSSFCellStyle.ALIGN_CENTER);
styleHeader.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
styleHeader.setWrapText(true);
XSSFCell cell1 = ateCell(0);
cell1.setCellStyle(styleHeader);
cell1.setCellValue(new XSSFRichTextString(header));
//创建第⼀⾏并设置⾏⾼
XSSFRow row = ateRow(1);
row.setHeight((short)400);
//标题样式设置
//设置字体
XSSFFont fontTitle = ateFont();
fontTitle.setFontName("宋体");
fontTitle.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
fontTitle.setFontHeightInPoints((short)10);
XSSFDataFormat format = ateDataFormat();
//设置单元格格式
XSSFCellStyle styleTitle = ateCellStyle();
styleTitle.setFont(fontTitle);
styleTitle.Format("@")); //设置输⼊格式为⽂本格式
styleTitle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
styleTitle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
styleTitle.setWrapText(true);
//主体样式设置
//设置字体
XSSFFont font = ateFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short)10);
//设置单元格格式
XSSFCellStyle style = ateCellStyle();
style.setFont(font);
style.Format("@")); //设置输⼊格式为⽂本格式
style.setAlignment(XSSFCellStyle.ALIGN_LEFT);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
//设置单元格内容
int columnCount = titles.size();
for(int i = 0; i < columnCount; i++){
XSSFCell cell = ateCell(i);
cell.setCellStyle(styleTitle);
cell.setCellValue(new (i)));
if(i==0){
sheet.setColumnWidth(i, 1500);
}else if(i==10){
sheet.setColumnWidth(i, 6000);
}else{
sheet.setColumnWidth(i, 4000);
}
sheet.setDefaultColumnStyle(i, style);
}
Integer seqStartRow = null, seqColumn = 0;
String seqPrevious = null;
//太平订单号
Integer orderNoStartrow = null, orderNoColumn = 1;
String orderNoPrevious = null;
//总公司
Integer parentcompanyStartrow = null, parentcompanyColumn = 2;
String parentcompanyPrevious = null;
//采购员机构
Integer invoicenameStartrow = null, invoicenameColumn = 3;
String invoicenamePrevious = null;
//部门
Integer departmentStartrow = null, departmentColumn = 4;
String departmentPrevious = null;
//采购员
Integer realnameStartrow = null, realnameColumn = 5;
String realnamePrevious = null;
//采购员账号
Integer usernameStartrow = null, usernameColumn = 6;
String usernamePrevious = null;
//下单时间
Integer datecreatedStartrow = null, datecreatedColumn = 7;
String datecreatedPrevious = null;
/
/⽀付⽅式
Integer paynameStartrow = null, paynameColumn = 8;
String paynamePrevious = null;
//应收凭证号
Integer vouchernoStartrow = null, vouchernoColumn = 9;
String vouchernoPrevious = null;
Map<String, Object> returnMap;
// 写⼊数据⾏
XSSFCell cell;
int rowIdx = 2;
Map<Object, Integer> seqMap = new HashMap<>();
int seq = 0;
String lastOrderNo = null;
try {
for(List<Object> dr : rows) {
row = ateRow(rowIdx);
for(int di=0; di < dr.size(); di++) {
if(di == 0) {
(1))) {
seq = (dr.get(1));
} else {
seq++;
seqMap.(1), seq);
怎么创建excel表格}
}
cell = ateCell(di);
cell.setCellStyle(style);
String cellValue = "";
if(Lang.(di))){
cellValue = "";
}else{
cellValue = dr.get(di)+"";
}
if(rowIdx - 2 != rows.size()-1){
if(di == 0){
returnMap =createOrMargedCell(sheet,row,style,rowIdx,String.valueOf(seq),seqPrevious,seqStartRow,seqColumn, dr.get(1).equals(lastOrderNo));
seqPrevious = (String) ("previousValue");
seqStartRow = (Integer) ("startRow");
} else if(di == 1){
returnMap =createOrMargedCell(sheet,row,style,rowIdx,cellValue,orderNoPrevious,orderNoStartrow,orderNoColumn,true);
orderNoPrevious = (String) ("previousValue");
orderNoStartrow = (Integer) ("startRow");
} else if(di == 2){
returnMap =createOrMargedCell(sheet,row,style,rowIdx,cellValue,parentcompanyPrevious,parentcompanyStartrow,(1).equals(lastOrderNo)); parentcompanyPrevious = (String) ("previousValue");
parentcompanyStartrow = (Integer) ("startRow");
} else if(di == 3){
returnMap =createOrMargedCell(sheet,row,style,rowIdx,cellValue,invoicenamePrevious,invoicenameStartrow,(1).equals(lastOrderNo));
invoicenamePrevious = (String) ("previousValue");
invoicenameStartrow = (Integer) ("startRow");
} else if(di == 4){
returnMap =createOrMargedCell(sheet,row,style,rowIdx,cellValue,departmentPrevious,departmentStartrow,(1).equals(lastOrderNo));
departmentPrevious = (String) ("previousValue");
departmentStartrow = (Integer) ("startRow");
} else if(di == 5){
returnMap =createOrMargedCell(sheet,row,style,rowIdx,cellValue,realnamePrevious,realnameStartrow,(1).equals(lastOrderNo));
realnamePrevious = (String) ("previousValue");
realnameStartrow = (Integer) ("startRow");
} else if(di == 6){
returnMap =createOrMargedCell(sheet,row,style,rowIdx,cellValue,usernamePrevious,usernameStartrow,(1).equals(lastOrderNo));
usernamePrevious = (String) ("previousValue");
usernameStartrow = (Integer) ("startRow");
} else if(di == 7){
returnMap =createOrMargedCell(sheet,row,style,rowIdx,cellValue,datecreatedPrevious,datecreatedStartrow,(1).equals(lastOrderNo));
datecreatedPrevious = (String) ("previousValue");
datecreatedStartrow = (Integer) ("startRow");
} else if(di == 8){
returnMap =createOrMargedCell(sheet,row,style,rowIdx,cellValue,paynamePrevious,paynameStartrow,(1).equals(lastOrderNo));
paynamePrevious = (String) ("previousValue");
paynameStartrow = (Integer) ("startRow");
} else if(di == 9){
returnMap =createOrMargedCell(sheet,row,style,rowIdx,cellValue,vouchernoPrevious,vouchernoStartr
ow,(1).equals(lastOrderNo));
vouchernoPrevious = (String) ("previousValue");
vouchernoStartrow = (Integer) ("startRow"); }else{
cell.setCellValue(new XSSFRichTextString(cellValue)); }
}else{
cell.setCellValue(new XSSFRichTextString(cellValue)); }
}
lastOrderNo = orderNoPrevious;
rowIdx ++;
}
} catch (Exception e) {
e.printStackTrace();
}
return xwb;
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论