现在很多java企业级应用要求进行数据在Excel上的操作,比较常用的技术是jxl和poi,对比而言两者都是比较简单实用,但在实现下拉列表功能上,jxl的功能接口没有poi的完善,比如实现下拉列表联动技术,就是poi比较简单好用,目前还在网上没有找到能够用jxl实现这个功能,希望读者能够仔细阅读jxl api,以求找到实现这一功能的接口。下面我来大概罗列用poi实现下拉列表联动的思路,然后贴出代码。
1.创建一个要显示的shee1t,并将非下拉框的数据依次写入sheet中,下拉框的数据要另外处理;
2.创建一个隐藏的sheet2,用于规律存放下拉框的数据,以行为单位,要计算关联的范围,记住sheet1的下拉框是和sheet2的数据关联的;
3.设置关联;
4.设置下拉框并可以增加下拉框操作验证;
主要核心代码:
package com.pccw.custom.ln.fas.ar.test;
import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import com.pccw.business.fas.ap.utils.excel.upload.UploadMappingCell;
import com.pccw.business.fas.ap.utils.excel.upload.UploadMappingReader;
import com.pccw.custom.ln.fas.ar.vo.SalesTaxAjustmentInfo;
import com.pccw.kernel.util.SystemProperties;
public class ExcelSelectUtils {
private static String EXCEL_HIDE_SHEET_NAME = "excelhidesheetname";
private static String HIDE_SHEET_NAME_LINE = "lineType";
private HashMap map = new HashMap();
private static String[] lineType = {"line","tax"};
private static String[] difReasonLine = {"line","a","b","c","d","e"};
private static String[] difReasonTax = {"tax","f","g","h","i","j)"};
private static CellStyle getTitleStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
//对齐方式设置
style.setAlignment(CellStyle.ALIGN_CENTER);
//边框颜色和宽度设置
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//设置背景颜色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFil