【JAVA poi Excel导入导出，可自定义单格样式和锁定单格，可设置单格下拉数据】
依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.16</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version> </dependency> 工具类
ps : 有三个类，一个实体，一个读取工具类，一个导出工具类
1，实体
import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import java.awt.*; import java.io.Serializable; import java.util.List; //数据实体 public class ExcelData implements Serializable {
private static final long serialVersionUID = L; / * 备注(可空，没有备注) */ private List<RemarkData> remarkList; / * 表头 */ private List<String> titles; / * 表头行高（可空，默认行高） */ private Integer titleHeight; / * 数据--不可锁定（Object为单格内容） */ private List<List<Object>> rows; / * 数据--可设置锁定不可编辑 */ private List<List<RowData>> rowsLock; / * 下拉框设置数据（可空，不设置下拉） */ private List<SelectData> selectDataList; / * 页签名称 */ private String name; / * 总数 */ private int total; public List<RemarkData> getRemarkList() {
return remarkList; } public void setRemarkList(List<RemarkData> remarkList) {
this.remarkList = remarkList; } public List<String> getTitles() {
return titles; } public void setTitles(List<String> titles) {
this.titles = titles; } public Integer getTitleHeight() {
return titleHeight; } public void setTitleHeight(Integer titleHeight) {
this.titleHeight = titleHeight; } public List<List<Object>> getRows() {
return rows; } public void setRows(List<List<Object>> rows) {
this.rows = rows; } public List<List<RowData>> getRowsLock() {
return rowsLock; } public void setRowsLock(List<List<RowData>> rowsLock) {
this.rowsLock = rowsLock; } public List<SelectData> getSelectDataList() {
return selectDataList; } public void setSelectDataList(List<SelectData> selectDataList) {
this.selectDataList = selectDataList; } public String getName() {
return name; } public void setName(String name) {
this.name = name; } public int getTotal() {
return total; } public void setTotal(int total) {
this.total = total; } public static RemarkData getRemarkData(String content,HorizontalAlignment horizontalAlignment,IndexedColors indexedColors,Color groundColor,Boolean isLocked,Integer mergedCell,Integer rowHeight,Integer fontSize) {
RemarkData remarkData= new RemarkData(); remarkData.setContent(content); remarkData.setHorizontalAlignment(horizontalAlignment); remarkData.setIndexedColors(indexedColors); remarkData.setGroundColor(groundColor); remarkData.setLocked(isLocked); remarkData.setMergedCell(mergedCell); remarkData.setRowHeight(rowHeight); remarkData.setFontSize(fontSize); return remarkData; } public static class RemarkData{
//内容 private String content; //水平位置布局（水平居中：HorizontalAlignment.CENTER，水平居左：HorizontalAlignment.LEFT） private HorizontalAlignment horizontalAlignment; //字体颜色（黑色：IndexedColors.BLACK，红色：IndexedColors.RED） private IndexedColors indexedColors; //背景颜色（可空，默认背景颜色） private Color groundColor; //是否锁定不可编辑 private Boolean isLocked=false; //合并几行单格 private Integer mergedCell; //行高（可空，取默认） private Integer rowHeight; //字体大小（可空，取默认） private Integer fontSize; public String getContent() {
return content; } public void setContent(String content) {
this.content = content; } public HorizontalAlignment getHorizontalAlignment() {
return horizontalAlignment; } public void setHorizontalAlignment(HorizontalAlignment horizontalAlignment) {
this.horizontalAlignment = horizontalAlignment; } public IndexedColors getIndexedColors() {
return indexedColors; } public void setIndexedColors(IndexedColors indexedColors) {
this.indexedColors = indexedColors; } public Color getGroundColor() {
return groundColor; } public void setGroundColor(Color groundColor) {
this.groundColor = groundColor; } public Boolean getLocked() {
return isLocked; } public void setLocked(Boolean locked) {
isLocked = locked; } public Integer getMergedCell() {
return mergedCell; } public void setMergedCell(Integer mergedCell) {
this.mergedCell = mergedCell; } public Integer getRowHeight() {
return rowHeight; } public void setRowHeight(Integer rowHeight) {
this.rowHeight = rowHeight; } public Integer getFontSize() {
return fontSize; } public void setFontSize(Integer fontSize) {
this.fontSize = fontSize; } } public static RowData getRowData(Object content,Boolean isLocked) {
RowData rowData= new RowData(); rowData.setContent(content); rowData.setLocked(isLocked); return rowData; } public static class RowData {
//内容 private Object content; //是否锁定不可编辑 private Boolean isLocked=false; public Object getContent() {
return content; } public void setContent(Object content) {
this.content = content; } public Boolean getLocked() {
return isLocked; } public void setLocked(Boolean locked) {
isLocked = locked; } } public static SelectData getSelectData(List<String> selectList,int firstRow,int lastRow,int firstCol,int lastCol) {
SelectData data= new SelectData(); data.setSelectList(selectList); data.setFirstRow(firstRow); data.setLastRow(lastRow); data.setFirstCol(firstCol); data.setLastCol(lastCol); return data; } public static class SelectData {
//下拉框中的值 List<String> selectList; //下标-从0开始 int firstRow; int lastRow; int firstCol; int lastCol; public List<String> getSelectList() {
return selectList; } public void setSelectList(List<String> selectList) {
this.selectList = selectList; } public int getFirstRow() {
return firstRow; } public void setFirstRow(int firstRow) {
this.firstRow = firstRow; } public int getLastRow() {
return lastRow; } public void setLastRow(int lastRow) {
this.lastRow = lastRow; } public int getFirstCol() {
return firstCol; } public void setFirstCol(int firstCol) {
this.firstCol = firstCol; } public int getLastCol() {
return lastCol; } public void setLastCol(int lastCol) {
this.lastCol = lastCol; } } } 2，读取工具类
import org.apache.commons.lang3.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.multipart.MultipartFile; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class ReadExcel {
protected final Log log = LogFactory.getLog(ReadExcel.class); private static DataFormatter formatter=new DataFormatter(); // 总行数 private int totalRows = 0; // 总条数 private int totalCells = 0; // 错误信息接收器 private String errorMsg; // 构造方法 public ReadExcel() {
} public String getErrorInfo() {
return errorMsg; } / * 此方法两个参数InputStream是字节流。isExcel2003是excel是2003还是2007版本 * * @param is * @param isExcel2003 * @return * @throws IOException */ public List<Map<String,Object>> getExcelInfo(InputStream is, boolean isExcel2003,List<String> columns,int startRow) {
List<Map<String,Object>> retList = null; try {
/ 根据版本选择创建Workbook的方式 */ Workbook wb = null; // 当excel是2003时 if (isExcel2003) {
wb = new HSSFWorkbook(is); } else {
log.info("before"); wb = new XSSFWorkbook(is); log.info("after"); } retList = readExcelValueMap(wb, columns,startRow); } catch (Exception e) {
log.error("getExcelInfo exception : ",e); log.info(e.getMessage()); } return retList; } / 描述 :读EXCEL文件 * @Description: * @param: @param fileName * @param: @param Mfile * @param: @param columns * @param: @param startRow >=0 * @param: @return * @return: List<Map<String,Object>> * @author wangjunqi * @Date 2019年3月8日 上午10:05:54 */ public List<Map<String, Object>> getExcelInfoMap(MultipartFile Mfile, List<String> columns, int startRow) {
List<Map<String, Object>> retList = null; InputStream is = null; try {
// 验证文件名是否合格 if (!validateExcel(Mfile.getOriginalFilename())) {
return null; } // 判断文件时2003版本还是2007版本 boolean isExcel2003 = true; if (isExcel2007(Mfile.getOriginalFilename())) {
isExcel2003 = false; } is = Mfile.getInputStream(); retList = getExcelInfo(is, isExcel2003,columns,startRow); is.close(); } catch (Exception e) {
log.error("getExcelInfo exception : ", e); } finally {
if (is != null) {
try {
is.close(); } catch (IOException e) {
is = null; e.printStackTrace(); } } } return retList; } // 得到总列数 public int getTotalCells() {
return totalCells; } // 得到总行数 public int getTotalRows() {
return totalRows; } / * 描述：验证EXCEL文件 * * @param filePath * @return */ public boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = "文件名不是excel格式"; return false; } return true; } / 读取Excel里面的信息 * @Description: * @param: @param wb * @param: @param columns * @param: @param startRow 起始行>=1 * @param: @return * @return: List<Map<String,Object>> * @author wangjunqi * @Date 2019年3月8日 上午9:59:46 */ private List<Map<String,Object>> readExcelValueMap(Workbook wb, List<String> columns, int startRow) {
startRow = startRow>=0?startRow:0; // 得到第一个shell Sheet sheet = wb.getSheetAt(0); // 得到Excel的行数 this.totalRows = sheet.getPhysicalNumberOfRows(); Row titleRow; // 得到Excel的列数(前提是有行数) if (totalRows >= 2 && sheet.getRow(1) != null) {
titleRow = sheet.getRow(1); this.totalCells = titleRow.getPhysicalNumberOfCells(); } List<Map<String,Object>> retList = new ArrayList<Map<String,Object>>(); for (int r = startRow; r < totalRows; r++) {
Row row = sheet.getRow(r); if (isRowEmpty(row)) {
continue; } Map<String,Object> retMap = new HashMap<String,Object>(); // 循环Excel的列 totalCells = columns.size(); int length = totalCells<columns.size()?totalCells:columns.size(); for (int c = 0; c < length; c++) {
Cell cell = row.getCell(c); String value = formatter.formatCellValue(cell); value = StringUtils.isNotEmpty(value) ? value : ""; retMap.put(columns.get(c) , value); } retList.add(retMap); } return retList; } public boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c); if (cell != null) {
return false; } } return true; } // 解决excel类型问题，获得数值 public String getValue(Cell cell) {
String value = ""; if (null == cell) {
return value; } return cell.getStringCellValue().toString(); } // @描述：是否是2003的excel，返回true是2003 public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$"); } //@描述：是否是2007的excel，返回true是2007 public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$"); } } 3，导出工具类
import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.usermodel.*; import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.http.HttpServletResponse; import java.awt.Color; import java.io.*; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; public class ExcelUtils {
static Logger logger= LoggerFactory.getLogger(ExcelUtils.class); / * 使用浏览器选择路径下载 * @param response * @param fileName * @param data * @throws Exception */ public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
// 告诉浏览器用什么软件可以打开此文件 response.setContentType("application/vnd.ms-excel;charset=UTF-8"); // 下载文件的默认名称 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8")); exportExcel(data, response.getOutputStream()); } public static int generateExcel(ExcelData excelData, String path) throws Exception {
File f = new File(path); FileOutputStream out = new FileOutputStream(f); return exportExcel(excelData, out); } private static int exportExcel(ExcelData data, OutputStream out) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook(); int rowIndex = 0; try {
String sheetName = data.getName(); if (null == sheetName) {
sheetName = "Sheet1"; } XSSFSheet sheet = wb.createSheet(sheetName); if(CollectionUtils.isEmpty(data.getRows()) && CollectionUtils.isNotEmpty(data.getRowsLock())){
//设置表格锁定 sheet.enableLocking(); } rowIndex = writeExcel(wb, sheet, data); wb.write(out); } catch (Exception e) {
logger.error("exportExcel系统异常：",e); } finally {
//此处需要关闭 wb 变量 out.close(); } return rowIndex; } / * 表不显示字段 * @param wb * @param sheet * @param data * @return */ // private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
// int rowIndex = 0; // writeTitlesToExcel(wb, sheet, data.getTitles()); // rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex); // autoSizeColumns(sheet, data.getTitles().size() + 1); // return rowIndex; // } / * 表显示字段 * @param wb * @param sheet * @param data * @return */ private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
if(CollectionUtils.isNotEmpty(data.getSelectDataList())){
//设置下拉列表填充--row和col从0开始 for(ExcelData.SelectData selectData : data.getSelectDataList()){
setDropdownToExcel(sheet,selectData.getSelectList().toArray(new String[selectData.getSelectList().size()]),selectData.getFirstRow(),selectData.getLastRow(),selectData.getFirstCol(),selectData.getLastCol()); } } int rowIndex = 0; rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles(),data.getRemarkList(),data.getTitleHeight()); if(CollectionUtils.isNotEmpty(data.getRows())){
rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex); }else if(CollectionUtils.isNotEmpty(data.getRowsLock())){
rowIndex = writeRowsToExcelAllowLock(wb, sheet, data.getRowsLock(), rowIndex); } //设置列宽 autoSizeColumns(sheet, data.getTitles().size() + 1); return rowIndex; } / * 设置表头 * * @param wb * @param sheet * @param titles * @return */ private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles, List<ExcelData.RemarkData> remarkList,Integer titleHeight) {
int rowIndex = 0; //设置备注 if(CollectionUtils.isNotEmpty(remarkList)){
for(ExcelData.RemarkData remarkData : remarkList){
//备注样式 XSSFCellStyle remarkStyleLeft = getTitleStyle(wb,remarkData.getHorizontalAlignment(),remarkData.getIndexedColors(),remarkData.getGroundColor(),remarkData.getLocked(),false,false,remarkData.getFontSize()); //设置备注行 Row remarkRow = sheet.createRow(rowIndex); if(remarkData.getRowHeight()!=null){
remarkRow.setHeightInPoints(remarkData.getRowHeight()); // 行高 }else{
remarkRow.setHeightInPoints(20); // 行高 } //合并单格 sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, remarkData.getMergedCell()-1)); Cell remarkCell = remarkRow.createCell(0); remarkCell.setCellValue(remarkData.getContent()); remarkCell.setCellStyle(remarkStyleLeft); rowIndex++; } } //标题样式水平居中 XSSFCellStyle titleStyle = getTitleStyle(wb,HorizontalAlignment.CENTER,IndexedColors.BLACK,null,true,true,false,null); //标题样式水平居中换行 XSSFCellStyle titleStyleWrapText = getTitleStyle(wb,HorizontalAlignment.CENTER,IndexedColors.BLACK,null,true,true,true,null); //设置标题 Row titleRow = sheet.createRow(rowIndex); if(titleHeight!=null){
titleRow.setHeightInPoints(titleHeight); // 行高 }else{
titleRow.setHeightInPoints(20); // 行高 } int colIndex = 0; for (String field : titles) {
Cell cell = titleRow.createCell(colIndex); cell.setCellValue(field); if(field.indexOf("\r\n")>-1) {
cell.setCellStyle(titleStyleWrapText); }else {
cell.setCellStyle(titleStyle); } colIndex++; } rowIndex++; return rowIndex; } / * 获取表头样式 */ private static XSSFCellStyle getTitleStyle(XSSFWorkbook wb,HorizontalAlignment horizontalAlignment,IndexedColors indexedColors,Color groundColor,boolean isLocked,boolean isSetBorder,boolean isWrapText,Integer fontSize) {
Font titleFont = wb.createFont(); //设置字体 titleFont.setFontName("Century"); //设置粗体 titleFont.setBold(true); //设置字号 if(fontSize==null){
fontSize=12; } titleFont.setFontHeightInPoints(Short.valueOf(String.valueOf(fontSize))); //设置颜色 titleFont.setColor(indexedColors.index); XSSFCellStyle titleStyle = wb.createCellStyle(); //水平居中 titleStyle.setAlignment(horizontalAlignment); //垂直居中 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); if(isWrapText){
titleStyle.setWrapText(true); } //设置图案颜色 if(groundColor!=null){
titleStyle.setFillForegroundColor(new XSSFColor(groundColor)); }else{
titleStyle.setFillForegroundColor(new XSSFColor(new Color(218, 218, 218))); } //设置图案样式 titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); titleStyle.setFont(titleFont); titleStyle.setLocked(isLocked); if(isSetBorder){
setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0))); } return titleStyle; } / * 设置内容 * * @param wb * @param sheet * @param rows * @param rowIndex * @return */ private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
int colIndex; XSSFCellStyle dataStyle = getCellStyle(wb,false,false); XSSFCellStyle dataStyleWrapText = getCellStyle(wb,true,false); if(rows!=null && !rows.isEmpty()){
for (List<Object> rowData : rows) {
Row dataRow = sheet.createRow(rowIndex); dataRow.setHeightInPoints(20); colIndex = 0; for (Object cellData : rowData) {
Cell cell = dataRow.createCell(colIndex); if (cellData != null) {
cell.setCellValue(cellData.toString()); if(cellData.toString().indexOf("\r\n")>-1) {
cell.setCellStyle(dataStyleWrapText); }else {
cell.setCellStyle(dataStyle); } } else {
cell.setCellValue(""); cell.setCellStyle(dataStyle); } colIndex++; } rowIndex++; } } return rowIndex; } / * 设置内容--支持设置锁定不可编辑 * * @param wb * @param sheet * @param rowsLock * @param rowIndex * @return */ private static int writeRowsToExcelAllowLock(XSSFWorkbook wb, Sheet sheet, List<List<ExcelData.RowData>> rowsLock, int rowIndex) {
int colIndex; XSSFCellStyle dataStyle = getCellStyle(wb,false,false); XSSFCellStyle dataStyleWrapText = getCellStyle(wb,true,false); XSSFCellStyle dataStyleLocked = getCellStyle(wb,false,true); XSSFCellStyle dataStyleWrapTextLocked = getCellStyle(wb,true,true); if(CollectionUtils.isNotEmpty(rowsLock)){
for (List<ExcelData.RowData> rowDataList : rowsLock) {
Row dataRow = sheet.createRow(rowIndex); dataRow.setHeightInPoints(20); colIndex = 0; for (ExcelData.RowData rowData : rowDataList) {
Cell cell = dataRow.createCell(colIndex); boolean isLock=rowData.getLocked(); String content=rowData.getContent() == null?"":rowData.getContent().toString(); cell.setCellValue(content); if(content.indexOf("\r\n")>-1) {
if(isLock){
cell.setCellStyle(dataStyleWrapTextLocked); }else{
cell.setCellStyle(dataStyleWrapText); } }else {
if(isLock){
cell.setCellStyle(dataStyleLocked); }else{
cell.setCellStyle(dataStyle); } } colIndex++; } rowIndex++; } } return rowIndex; } / * 自动调整列宽 * * @param sheet * @param columnNumber */ private static void autoSizeColumns(Sheet sheet, int columnNumber) {
for (int i = 0; i < columnNumber; i++) {
// int orgWidth = sheet.getColumnWidth(i); sheet.autoSizeColumn(i, true); sheet.setColumnWidth(i, 3500); // int newWidth = (int) (sheet.getColumnWidth(i) + 100); // if (newWidth > orgWidth) {
// sheet.setColumnWidth(i, newWidth); // } else {
// sheet.setColumnWidth(i, orgWidth); // } } } / * 获取单格样式 */ private static XSSFCellStyle getCellStyle(XSSFWorkbook wb,boolean isWrapText,boolean isLocked) {
Font dataFont = wb.createFont(); dataFont.setFontName("SimHei"); dataFont.setFontHeightInPoints((short) 12); dataFont.setColor(IndexedColors.BLACK.index); XSSFCellStyle dataStyle = wb.createCellStyle(); if(isWrapText){
dataStyle.setWrapText(true); } dataStyle.setAlignment(HorizontalAlignment.LEFT);//水平布局 dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直布局 dataStyle.setFont(dataFont); dataStyle.setLocked(isLocked); setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0))); return dataStyle; } / * 设置边框 * * @param style * @param border * @param color */ private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
style.setBorderTop(border); style.setBorderLeft(border); style.setBorderRight(border); style.setBorderBottom(border); style.setBorderColor(BorderSide.TOP, color); style.setBorderColor(BorderSide.LEFT, color); style.setBorderColor(BorderSide.RIGHT, color); style.setBorderColor(BorderSide.BOTTOM, color); } / * <p> * Description: 设置下拉框到excel * </p> * @author tanxin * @param sheet * @param selectList 下拉框中的值 * @param firstRow 下标-从0开始 * @param lastRow * @param firstCol * @param lastCol */ private static void setDropdownToExcel(Sheet sheet,String[] selectList, int firstRow, int lastRow, int firstCol, int lastCol ){
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol) ; DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper(); DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(selectList); DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions); //处理Excel兼容性问题 if (createValidation instanceof XSSFDataValidation) {
createValidation.setSuppressDropDownArrow(true); createValidation.setShowErrorBox(true); } else {
createValidation.setSuppressDropDownArrow(false); } sheet.addValidationData(createValidation); } } 测试类
//导入 @ResponseBody @PostMapping("/imports") public ResultVo<List<JSONObject>> imports(@ApiParam("导入文件") @RequestParam("file") MultipartFile file ) {
try{
// 处理EXCEL ReadExcel readExcel = new ReadExcel(); // 获得解析excel方法，此处按列排序设置key，userName为第一列 List<String> columns = new ArrayList<>(); columns.add("userName"); columns.add("age"); columns.add("sex"); //读取数据，数字表示从第几行开始，第一行为0 List<Map<String, Object>> retList = readExcel.getExcelInfoMap(file,columns,1); for(Map<String, Object> obj : retList) {
String userName=obj.get("userName")==null?"":obj.get("userName").toString(); } }catch (Exception e){
e.printStackTrace(); } } //导出 @ResponseBody @PostMapping("/download") public void download(@RequestBody Bean Bean, HttpServletResponse response ) throws Exception {
//设置下拉填充列表数据 List<String> nameSelect=new ArrayList<>(); nameSelect.add("1"); nameSelect.add("2"); nameSelect.add("3"); //设置下拉，第5-51行，4-11列，单格填充下拉数据 List<ExcelData.SelectData> selectDataList=new ArrayList<>(); selectDataList.add(ExcelData.getSelectData(nameSelect,4,50,3,10)); //备注 int remarkMergedCell=11;//设置单行单格合并数 List<ExcelData.RemarkData> remarkList=new ArrayList<>(); remarkList.add(ExcelData.getRemarkData("备注1", HorizontalAlignment.LEFT, IndexedColors.WHITE,new Color(62, 30, 102),true,remarkMergedCell,30,20)); remarkList.add(ExcelData.getRemarkData("备注2", HorizontalAlignment.LEFT, IndexedColors.RED,new Color(255, 255, 255),true,remarkMergedCell,null,null)); remarkList.add(ExcelData.getRemarkData("备注3", HorizontalAlignment.LEFT, IndexedColors.BLACK,new Color(255, 255, 255),true,remarkMergedCell,null,null)); //设置数据 List<List<ExcelData.RowData>> rowsLock = new ArrayList<>(); for(int i=0;i<50;i++){
List<ExcelData.RowData> row = new ArrayList<>(); row.add(ExcelData.getRowData("张三",true));//为true则锁定单格不可编辑 row.add(ExcelData.getRowData("20",true)); row.add(ExcelData.getRowData("男",true)); rowsLock.add(row); } ExcelData data = new ExcelData(); data.setName("数据"); //设置表头 List<String> titles = new ArrayList<>(); titles.add("姓名"); titles.add("年龄"); titles.add("性别"); data.setRemarkList(remarkList); data.setTitles(titles); data.setTitleHeight(35);//标题行高 data.setRowsLock(rowsLock);//设置导出数据 data.setSelectDataList(selectDataList);//下拉数据设置 ExcelUtils.exportExcel(response, "数据文件" ,data); }
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/bian-cheng-ji-chu/85142.html