正文
poi之Excel下载之详细设置
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
1、设置标题格式
/**
* HEAD样式
*
* @param workbook
* @param sheet
*/
public void setHeadCellStyles(HSSFWorkbook workbook, HSSFSheet sheet) {
headStyle = workbook.createCellStyle();
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);// 设置字体大小
headStyle.setFont(font);
}
2、设置列头样式
/**
* 列头样式
* @param workbook
* @param sheet
*/
public void setTitleCellStyles(HSSFWorkbook workbook, HSSFSheet sheet)
{
titleStyle = workbook.createCellStyle(); // 设置边框
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置背景色
titleStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置居中
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置字体
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11); // 设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
titleStyle.setFont(font);// 选择需要用到的字体格式
// 设置自动换行
titleStyle.setWrapText(true);
// 设置列宽 ,第一个参数代表列id(从0开始),第2个参数代表宽度值
sheet.setColumnWidth(0, 7000);//
// sheet.setColumnWidth(1, 7000);//
sheet.setColumnWidth(1, 4000);//
sheet.setColumnWidth(2, 4000);//
sheet.setColumnWidth(3, 4000);//
sheet.setColumnWidth(4, 7000);//
sheet.setColumnWidth(5, 7000);//
sheet.setColumnWidth(6, 4000);//
sheet.setColumnWidth(7, 4000);//
sheet.setColumnWidth(8, 4000);//
sheet.setColumnWidth(9, 4000);//
sheet.setColumnWidth(10, 4000);//
}
3、设置数据样式
/**
* 数据样式
*
* @param workbook
* @param sheet
*/
public void setDataCellStyles(HSSFWorkbook workbook, HSSFSheet sheet) {
dataStyle = workbook.createCellStyle(); // 设置边框
dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置背景色
dataStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
dataStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置居中
dataStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
// 设置字体
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11); // 设置字体大小
dataStyle.setFont(font);// 选择需要用到的字体格式
// 设置自动换行
dataStyle.setWrapText(true);
}
4、创建隐藏页和数据域（省市区三级联动下拉框数据隐藏域设置）
/**
* 创建隐藏页和数据域（省市区三级联动下拉框数据隐藏域设置）
* @param workbook
* @param hideSheetName
*/
public void creatHideSheet(HSSFWorkbook workbook) {
/****************************************************** 创建省市区 ***************************************************/
HSSFSheet factoryAndModelSheet = workbook.createSheet("factoryAndModelSheet");// 隐藏一些信息
/*************************************************** 省-市 start ***************************************************/
//省份
List<Area> proviceList = 获得省份列表;
List<String> rowList = null;
String provinceid = "";
String provinceCity = "";
Name name;
for(int i=0;i<proviceList.size();i++){
HSSFRow pfModelRow = factoryAndModelSheet.createRow(i);
rowList = new ArrayList<String>();
provinceid = proviceList.get(i).getId().toString();
provinceCity = proviceList.get(i).getProvinceCity();
rowList.add(provinceCity + "_" + provinceid);
List<Area> cityList = 根据省份获得城市列表;
// 添加“省--->市” 名称
name = workbook.createName();
name.setNameName( provinceCity+ "_" + provinceid);
for(int j=0; j<cityList.size(); j++){
Area area = cityList.get(j);
rowList.add(area.getProvinceCity()+"_"+area.getId().toString());
}
this.creatRow(pfModelRow, rowList);
name.setRefersToFormula("factoryAndModelSheet!$B$" + (i + 1) + ":$"
+ this.getcellColumnFlag(cityList.size() + 1) + "$"
+ (i + 1));
}
name = workbook.createName();
name.setNameName("provice");
name.setRefersToFormula("factoryAndModelSheet!$A$1:$A$"+ proviceList.size());
/*************************************************** 省-市 end ***************************************************/
/*************************************************** 市-区 start ***************************************************/
//市
int sm = proviceList.size()+1;
List<String> cityNList = null;
List<Area> cityareaList = new LinkedList<Area>();
String cityid = "";
String cityCity = "";
//将所有的市加之cityareaList
for(int i=0;i<proviceList.size();i++){
provinceid = proviceList.get(i).getId().toString();
List<Area> cityList = 获得城市列表;
cityareaList.addAll(cityList);//加至List
}
for(int i=0;i<cityareaList.size();i++){
HSSFRow pfModelRow = factoryAndModelSheet.createRow(sm+i);
cityNList = new ArrayList<String>();
cityid = cityareaList.get(i).getId().toString();
cityCity = cityareaList.get(i).getProvinceCity();
cityNList.add(cityCity + "_" + cityid);
List<Area> cityList = 根据城市查询区域列表;
// 添加“市--->区” 名称
name = workbook.createName();
name.setNameName( cityCity+ "_" + cityid);
for(int j=0; j<cityList.size(); j++){
Area area = cityList.get(j);
cityNList.add(area.getProvinceCity()+"_"+area.getId().toString());
}
this.creatRow(pfModelRow, cityNList);
name.setRefersToFormula("factoryAndModelSheet!$B$" + (sm+i + 1) + ":$"
+ this.getcellColumnFlag(cityList.size() + 1) + "$"
+ (sm+i + 1));
}
name = workbook.createName();
name.setNameName("citycityname");
name.setRefersToFormula("factoryAndModelSheet!$A$"+sm+":$A$"+ cityareaList.size());
/*************************************************** 市-区 end ***************************************************/
// 设置隐藏页标志
workbook.setSheetHidden(workbook.getSheetIndex("factoryAndModelSheet"),true);
}
5、创建标题和列头数据
/**
* 创建标题应用列头
* @param userinfosheet1
* @param userName
*/
public void creatAppRowHead(HSSFSheet userinfosheet1, String headName) {
// 设置标题
HSSFRow rowHead = userinfosheet1.createRow(0);
userinfosheet1.addMergedRegion(new Region(0, (short) 0, 0, (short) 21));
POIUtils.createCell(rowHead, (short) 0, headName, headStyle);
rowHead.setHeight((short) (27 * 20)); // 设置表头
HSSFRow row = userinfosheet1.createRow(1); HSSFCell merchantIdCell = row.createCell(0);
merchantIdCell.setCellValue("一");
merchantIdCell.setCellStyle(titleStyle); // HSSFCell mrchtNameCell = row.createCell(1);
// mrchtNameCell.setCellValue("二");
// mrchtNameCell.setCellStyle(titleStyle); HSSFCell merchantEnameCell = row.createCell(1);
merchantEnameCell.setCellValue("二");
merchantEnameCell.setCellStyle(titleStyle); HSSFCell abbrCnameCell = row.createCell(2);
abbrCnameCell.setCellValue("三");
abbrCnameCell.setCellStyle(titleStyle); HSSFCell abbrEnameCell = row.createCell(3);
abbrEnameCell.setCellValue("四");
abbrEnameCell.setCellStyle(titleStyle); HSSFCell mccIdCell = row.createCell(4);
mccIdCell.setCellValue("五");
mccIdCell.setCellStyle(titleStyle); HSSFCell addressCell = row.createCell(5);
addressCell.setCellValue("六");
addressCell.setCellStyle(titleStyle); HSSFCell provinceCell = row.createCell(6);
provinceCell.setCellValue("七");
provinceCell.setCellStyle(titleStyle); HSSFCell cityNoCell = row.createCell(7);
cityNoCell.setCellValue("八");
cityNoCell.setCellStyle(titleStyle); HSSFCell zoneCell = row.createCell(8);
zoneCell.setCellValue("九");
zoneCell.setCellStyle(titleStyle); HSSFCell telephoneCell = row.createCell(9);
telephoneCell.setCellValue("十");
telephoneCell.setCellStyle(titleStyle); HSSFCell managerCell = row.createCell(10);
managerCell.setCellValue("十一");
managerCell.setCellStyle(titleStyle);
}
6、设置下拉框
/**
* 添加下拉框限制
* @param sheet1
*/
public void createSelectValidate(HSSFSheet sheet1) { //省，第7列
DVConstraint constraintPosType = DVConstraint.createFormulaListConstraint("provice");
// 作用域：起始行、终止行、起始列、终止列
CellRangeAddressList regionsPosType = new CellRangeAddressList(2, 499,6, 6);
DataValidation data_validation_PosType = new HSSFDataValidation(regionsPosType, constraintPosType);
sheet1.addValidationData(data_validation_PosType); //市，第8列
DVConstraint constraintcityNo = DVConstraint.createFormulaListConstraint("INDIRECT($G$3:$G$500)");
// 作用域：起始行、终止行、起始列、终止列
CellRangeAddressList regionscityNo = new CellRangeAddressList(2, 499,7, 7);
DataValidation data_validation_cityNo = new HSSFDataValidation(regionscityNo, constraintcityNo);
sheet1.addValidationData(data_validation_cityNo); //区，第9列
DVConstraint constraintmanager = DVConstraint.createFormulaListConstraint("INDIRECT($H$3:$H$500)");
// 作用域：起始行、终止行、起始列、终止列
CellRangeAddressList regionsmanager = new CellRangeAddressList(2, 499,8, 8);
DataValidation data_validation_manager = new HSSFDataValidation(regionsmanager, constraintmanager);
sheet1.addValidationData(data_validation_manager); }
7、工具方法-创建一列数据
/**
* 创建一列数据
*
* @param currentRow
* @param textList
*/
public void creatRow(HSSFRow currentRow, List<String> textList) {
if (textList != null && textList.size() > 0) {
int i = 0;
for (String cellValue : textList) {
HSSFCell userNameLableCell = currentRow.createCell(i++);
userNameLableCell.setCellValue(cellValue);
}
}
} // 根据数据值确定单元格位置（比如：28-AB）
private String getcellColumnFlag(int num) {
String columFiled = "";
int chuNum = 0;
int yuNum = 0;
if (num >= 1 && num <= 26) {
columFiled = this.doHandle(num);
} else {
chuNum = num / 26;
yuNum = num % 26; columFiled += this.doHandle(chuNum);
columFiled += this.doHandle(yuNum);
}
return columFiled;
} private String doHandle(final int num) {
String[] charArr = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V",
"W", "X", "Y", "Z" };
return charArr[num - 1].toString();
}
