一、需求:
1、第一列 不可编辑(对应Excel中的保护工作表),其他列可以编辑
2、第五列和第十四列为下拉框
3、可以在导出后新增行数据
二、期间遇到的问题
1、无法设置成不可编辑
2、解决1后未满足需求3,即在导出的Excel中直接编辑新行是提示写保(原因是Excel单元格默认是锁定状态,而保护工作表是保护的锁定的单元格),思路:直接操作列
3、解决2后,发现直接编辑新行时下拉框没起到作用,只是导出的数据有下拉框
注意:
1、解决setDefaultColumnStyle无效的方法
你会发现,设置好style以后,column会隐藏起来,原因在createCellStyle的时候,默认宽度为0,所以会被隐藏起来了,所以需要设置宽度
//设置列格式,注释1
sheet.setColumnWidth(i, 4000); //设置宽度
//行号,样式
sheet.setDefaultColumnStyle(i, unlockstyle);
说明:不考虑代码规范性,只作为参考使用
解决问题方法:
1、设置单元格保护(灵感来自于Excel操作,先锁定,再设置保护工作表)
//单元格锁定的样式
XSSFCellStyle lockstyle = workBook.createCellStyle();
lockstyle.setLocked(true);
lockstyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
lockstyle.setFillForegroundColor(IndexedColors.RED.getIndex());
//单元格不锁定的样式
XSSFCellStyle unlockstyle = workBook.createCellStyle();
unlockstyle.setLocked(false);
//这里设置单元格样式,需要保护的就设置成锁定,不需要保护的就设置成不锁定..
......
// 设置锁定的单元格为写保护 注释2
//sheet表加密:等效excel的审阅菜单下的保护工作表
//sheet.protectSheet(new String("333"));//333是密码
sheet.enableLocking();
CTSheetProtection sheetProtection = sheet.getCTWorksheet().getSheetProtection();
sheetProtection.setSelectLockedCells(false);
sheetProtection.setSelectUnlockedCells(false);
sheetProtection.setFormatCells(true);
sheetProtection.setFormatColumns(true);
sheetProtection.setFormatRows(true);
sheetProtection.setInsertColumns(true);
sheetProtection.setInsertRows(false);
sheetProtection.setInsertHyperlinks(true);
sheetProtection.setDeleteColumns(true);
sheetProtection.setDeleteRows(true);
sheetProtection.setSort(false);
sheetProtection.setAutoFilter(false);
sheetProtection.setPivotTables(true);
sheetProtection.setObjects(true);
sheetProtection.setScenarios(true);
2、设置列默认未锁定
//设置列格式,注释1
sheet.setColumnWidth(i, 4000); //设置宽度
//行号,样式
sheet.setDefaultColumnStyle(i, unlockstyle);
3、设置下拉框,没找到直接设置列的方法,所以将行设置的大了些
//运输方式
String[] textList1 = new String[]{"值1","值二","值三"};
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint1 = (XSSFDataValidationConstraint) dvHelper
.createExplicitListConstraint(textList1);
//操作类型
String[] textList2 = {"I","U","D"};
XSSFDataValidationConstraint dvConstraint2 = (XSSFDataValidationConstraint) dvHelper
.createExplicitListConstraint(textList2);
//运输方式下拉 首行,末行,首列,末列
addressList1 = new CellRangeAddressList(1,65536,4,4);
dataValidation1 = (XSSFDataValidation) dvHelper.createValidation(
dvConstraint1, addressList1);
// 07默认setSuppressDropDownArrow(true);
// validation.setSuppressDropDownArrow(true);
// validation.setShowErrorBox(true);
sheet.addValidationData(dataValidation1);
//操作类型下拉 首行,末行,首列,末列
addressList2 = new CellRangeAddressList(1,65536,13,13);
dataValidation2 = (XSSFDataValidation) dvHelper.createValidation(
dvConstraint2, addressList2);
// 07默认setSuppressDropDownArrow(true);
// validation.setSuppressDropDownArrow(true);
// validation.setShowErrorBox(true);
sheet.addValidationData(dataValidation2);
所有代码
@Override
@Transactional(propagation = Propagation.REQUIRED)
public ResponseData exportData(String rowdatas,HttpServletResponse response){
ResponseData responseData = new ResponseData();
try {
JSONArray rowArray = JSONArray.fromObject(rowdatas);
List<RoutingRulesSetDto> resultlist = new ArrayList<RoutingRulesSetDto>();
List<RoutingRulesSetDto> rowlist = (List<RoutingRulesSetDto>) JSONArray.toCollection(rowArray, RoutingRulesSetDto.class);
resultlist = routingRuleMapper.selectByIds(rowlist);
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet sheet = workBook.createSheet();
//设置页名称
workBook.setSheetName(0, "sheet名");
//默认宽度
sheet.setDefaultColumnWidth(15);
//设置title样式
XSSFCellStyle titleStyle = workBook.createCellStyle();
titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
//单元格锁定的样式
XSSFCellStyle lockstyle = workBook.createCellStyle();
lockstyle.setLocked(true);
lockstyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
lockstyle.setFillForegroundColor(IndexedColors.RED.getIndex());
//单元格不锁定的样式
XSSFCellStyle unlockstyle = workBook.createCellStyle();
unlockstyle.setLocked(false);
//设置导出表头
String[] header = new String[]{"ID(不可编辑)","字段1","字段2","字段3",
"字段4","字段5","字段6","字段7",
"字段8","字段9","字段10","字段11","字段12","字段13"};
//创建表头行
XSSFRow row = sheet.createRow(0);
XSSFCell cell;
//插入Excel表头
for (short i = 0; i < header.length;i++) {
cell =row.createCell(i);
if(i == 0){
cell.setCellStyle(lockstyle);
}else if(i==6 || i==7 || i==8 || i==10 || i==11 || i==12){
//设置列格式,注释1
sheet.setColumnWidth(i, 4000);
sheet.setDefaultColumnStyle(i, unlockstyle);
}else{
cell.setCellStyle(titleStyle);
//设置列格式,注释1
sheet.setColumnWidth(i, 4000);
sheet.setDefaultColumnStyle(i, unlockstyle);
}
XSSFRichTextString text = new XSSFRichTextString(header[i]);
cell.setCellValue(text);
}
//下拉列表
CellRangeAddressList addressList1 = null;
XSSFDataValidation dataValidation1 = null;
CellRangeAddressList addressList2 = null;
XSSFDataValidation dataValidation2 = null;
//运输方式
String[] textList1 = new String[]{"值1","值二","值三"};
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint1 = (XSSFDataValidationConstraint) dvHelper
.createExplicitListConstraint(textList1);
//操作类型
String[] textList2 = {"I","U","D"};
XSSFDataValidationConstraint dvConstraint2 = (XSSFDataValidationConstraint) dvHelper
.createExplicitListConstraint(textList2);
//运输方式下拉 首行,末行,首列,末列
addressList1 = new CellRangeAddressList(1,65536,4,4);
dataValidation1 = (XSSFDataValidation) dvHelper.createValidation(
dvConstraint1, addressList1);
// 07默认setSuppressDropDownArrow(true);
// validation.setSuppressDropDownArrow(true);
// validation.setShowErrorBox(true);
sheet.addValidationData(dataValidation1);
//操作类型下拉 首行,末行,首列,末列
addressList2 = new CellRangeAddressList(1,65536,13,13);
dataValidation2 = (XSSFDataValidation) dvHelper.createValidation(
dvConstraint2, addressList2);
// 07默认setSuppressDropDownArrow(true);
// validation.setSuppressDropDownArrow(true);
// validation.setShowErrorBox(true);
sheet.addValidationData(dataValidation2);
//遍历插入数据
for (int i = 0; i < resultlist.size(); i++) {
row = sheet.createRow(i+1);
//
cell = row.createCell(0);
cell.setCellValue(resultlist.get(i).getId() == null ? "" : resultlist.get(i).getId());
cell.setCellStyle(lockstyle);
//
cell = row.createCell(1);
cell.setCellValue(resultlist.get(i).getOrCustomerId() == null ? "" : resultlist.get(i).getOrCustomerId());
cell.setCellStyle(unlockstyle);
// cell = row.createCell(2);
cell.setCellValue(resultlist.get(i).getItemGid() == null ? "" : resultlist.get(i).getItemGid());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(3);
cell.setCellValue(resultlist.get(i).getCommodityGid() == null ? "" : resultlist.get(i).getCommodityGid());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(4);
cell.setCellValue(resultlist.get(i).getTransportModeGid() == null ? "" : resultlist.get(i).getTransportModeGid());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(5);
cell.setCellValue(resultlist.get(i).getOrSourceLocationGid() == null ? "" : resultlist.get(i).getOrSourceLocationGid());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(6);
cell.setCellValue(resultlist.get(i).getOrSourceLocationName() == null ? "" : resultlist.get(i).getOrSourceLocationName());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(7);
cell.setCellValue(resultlist.get(i).getSourceProvince() == null ? "" : resultlist.get(i).getSourceProvince());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(8);
cell.setCellValue(resultlist.get(i).getSourceCity() == null ? "" : resultlist.get(i).getSourceCity());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(9);
cell.setCellValue(resultlist.get(i).getOrDestLocationGid() == null ? "" : resultlist.get(i).getOrDestLocationGid());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(10);
cell.setCellValue(resultlist.get(i).getOrDestLocationName() == null ? "" : resultlist.get(i).getOrDestLocationName());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(11);
cell.setCellValue(resultlist.get(i).getDestProvince() == null ? "" : resultlist.get(i).getDestProvince());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(12);
cell.setCellValue(resultlist.get(i).getDestCity() == null ? "" : resultlist.get(i).getDestCity());
cell.setCellStyle(unlockstyle);
//
cell = row.createCell(13);
cell.setCellValue("U");
cell.setCellStyle(unlockstyle);
}
// 设置锁定的单元格为写保护 注释2
//sheet表加密:等效excel的审阅菜单下的保护工作表
//sheet.protectSheet(new String("333"));//333是密码
sheet.enableLocking();
CTSheetProtection sheetProtection = sheet.getCTWorksheet().getSheetProtection();
sheetProtection.setSelectLockedCells(false);
sheetProtection.setSelectUnlockedCells(false);
sheetProtection.setFormatCells(true);
sheetProtection.setFormatColumns(true);
sheetProtection.setFormatRows(true);
sheetProtection.setInsertColumns(true);
sheetProtection.setInsertRows(false);
sheetProtection.setInsertHyperlinks(true);
sheetProtection.setDeleteColumns(true);
sheetProtection.setDeleteRows(true);
sheetProtection.setSort(false);
sheetProtection.setAutoFilter(false);
sheetProtection.setPivotTables(true);
sheetProtection.setObjects(true);
sheetProtection.setScenarios(true);
//创建流对象,并写入workBook
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workBook.write(os);
} catch (Exception e) {
e.printStackTrace();
}
Date now = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
String data1 = dateFormat.format( now );
byte[] content = os.toByteArray();
InputStream iStream = new ByteArrayInputStream(content);
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(("导出文件名" +data1+ ".xlsx").getBytes("gbk"), "iso-8859-1"));
ServletOutputStream oStream = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(iStream);
bos = new BufferedOutputStream(oStream);
byte[] buff = new byte[2048];
int bytesRead;
while(-1 != (bytesRead = bis.read(buff,0,buff.length))){
bos.write(buff,0 ,bytesRead);
}
} catch (Exception e) {
throw e;
} finally {
try {
if(bis != null){
bis.close();
}
if(bos != null){
bos.close();
}
if(iStream != null){
iStream.close();
}
if(oStream != null){
oStream.close();
}
if(os != null){
os.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
responseData.setSuccess(true);
responseData.setCode("S");
return responseData;
} catch (Exception e) {
e.printStackTrace();
responseData.setSuccess(false);
responseData.setCode("E");
responseData.setMessage("程序异常");
return responseData;
}
}