/**
*创建模板文件
*/
public class CreateTemplate{
public static void main(String[] args){
//获取xml路径
String path = System.getProperty("user.dir") + "/bin/student.xml";
File file = new File(path);
SAXBuilder builder = new SAXBuilder();
try{
//解析xml
Document parse = builder.build(file);
//创建Excel
HSSFWorkbook wb = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = wb.createSheet("Sheet0");
//设置单元格样式
HSSFCellStyle cellStyle = wb.createCellStyle();
//单元格居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//获取xml文件根节点
Element root = parse.getRootElement();
//获取模板名称
String templateName = root.getAttribute("name").getValue();
int rownum = 0;
int column = 0;
//设置列宽
Element colgroup = root.getChild("colgroup");
setColumnWidth(sheet,colgroup);
//设置标题
Element title = root.getChild("title");
List<Element> trs = title.getChildren("tr");
for(int i = 0; i < trs.size(); i++){
Element tr = trs.get(i);
List<Element> tds = tr.getChildren("td");
HSSFRow row = sheet.createRow(rownum);
for(column = 0; column < tds.size(); column++){
Element td = tds.get(column);
HSSFCel cell = row.createCell(column);
Arrtibute rowSpan = td.getAttribute("rowspan");
Arrtibute colSpan = td.getAttribute("colspan");
Arrtibute value= td.getAttribute("value");
if(value != null){
String val = value.getValue();
cell.setCellValue(val);
int rspan = rowSpan.getIntValue()-1;
int cspan = colSpan.getIntValue()-1;
//设置字体
HSSFFont font = wb.createFont();
font.setFontName("仿宋_GB2312");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
//font.setFontHeight((short)12);
font.setFontHeightInPoints((short)12);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
//合并单元格
sheet.addMergeRegion(new CellRangeAddress(rspan,rspan,0,cspan));
}
}
rownum++;
}
//设置表头
Element thead = root.getChild("thead");
trs = thead.getChildren("tr");
for(int i = 0; i < trs.size(); i++){
Element tr = trs.get(i);
HSSFRow row = sheet.createRow(rownum);
List<Element> ths = tr.getChildren("th");
for(column = 0; column < ths.size(); column++){
Element td = ths.get(column);
Attribute valueAttr = th.geAttribute("value");
HSSFCell cell = row.createCell(column);
if(valueAttr != null){
String value = valueAttr.getValue();
cell.setCellValue(value);
}
}
rownum++;
}
//设置数据区域的样式
Element tbody = root.getChild("tbody");
Element trs = tbody.getChild("tr");
int repeat = tr.getAttribute("repeat").getIntValue();
List<Element> tds = tr.getChildren("td");
for(int i = 0; i <repeat; i++){
HSSFRow row = sheet.createRow(rownum);
for(column = 0; column < tds.size(); column++){
Element td = tds.get(i);
HSSFCell cell = row.createCell(column);
setType(wb,cell,td);
}
rownum++;
}
//生产Excel导入模板_保存到本地
File tempFile = new File("e:/" + templateName + ".xls");
tempFile.delete();// 先删除一下在创建
tempFile.createNewFile();
//输出流写入到Excel中
FileOutputStream stream = FileUtils.openOutputStream(tempFile);
//写入
wb.write(stream);
stream.close();
}catch(Exception e){
e.printStackTrace();
}
}
/**
*创建模板文件
*/
private static void setColumnWidth(HSSFSheet sheet, Element colgroup){
List<Element > cols = colgroup.getChildren("col");
for(int i = 0; i < cols.size(); i++){
Elemetn col = cols.get(i);
Attribute width = col.getAttribute("width");
String unit = width.getValue.replaceAll("[0-9,\.]","");
String value = width.getValue().replaceAll(unit,"");
int v =0 ;
if(StringUtils.isBlank(unit) || "px".endsWith(unit)){
v = Math.round(Float.parseFloat(value)*37F);
}else if(){
v = Math.round(Float.parseFloat(value)*267.5F);
}
sheet.setColumnWidth(i,v);
}
}
/**
*设置单元格样式
*/
private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td){
Attribute typeAtrr = td.getAttribute("type");
String type = typeAttr.getValue();
//定义格式化的东西，及单元格样式
HSSFDataFormat format = wb.createDataFormat();
HSSFCellStyle celllStyle = wb.createCell();
//判断单元格样式属于那种类型
if("NUMERIC".equalsIgnoreCase(type)){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
Attribute formatAttr = td.getAttribute("format");
String formatValue = formatAttr.getValue();
//如果格式化没有设置的化要给一个默认值
formatValue = StringUtils.isNotBlank(formatValue)?formatValue:"#,##0.00";
cellStyle.setDataFormat(format.getFormat(formatValue));
}else if("STRING".equalsIgnoreCase(type)){
cell.setCellValue("");
cell.setCellType(HSSFCell.CELLL_TYPE_STRING);
celStyle.setDataFormat(format.getFormat("@"));//@表示文本
}else if("DATE".equalsIgnoreCase(type)){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellStyle.setDataFormat(format.getFormat("yyyy-m-d"););
}else if("ENUM".equalsIgnoreCase(type)){
CellRangeAddressList regions =
new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
//获取枚举的值
Attribute enumAttr = td.getAttribute("format");
String enumValue = enumAttr.getValue();
//加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));//参数值就是下拉列表的值
//加入数据有效性对象
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
//设置到Excel中
wb.getSheetAt(0).addValidationData(dataValidation);
}
cell.setCellStyle(cellStyle);
}
}