java导出excel模板，java设置excel模板只能输入时间，数字，excel输入限制
没废话，直接上代码
package com.artfess.rfdm.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import com.zx.system.annotation.ControllerMapping;
import com.zx.system.bmp.client.CommonController;
import com.zx.system.util.SDO;
import com.zx.system.util.data.jdbc.SQLQuery;
@Controller
@ControllerMapping({ "excel" })
public class ExcelController extends CommonController {
@Autowired
protected SQLQuery sqlQuery;
/**
* 导出
* @param blacklist
* @param model
* @return
*/
public String exportTable () throws IOException {
HttpServletRequest request = getRequest();
HttpServletResponse response = getResponse();
request.setCharacterEncoding("UTF-8");
SDO params = this.getSDO();
String sysModuleTableId = params.getString("sysModuleTableId");
List<Map<String, Object>> getById = selectByid(sysModuleTableId);
String resourceCatalogId = String.valueOf(getById.get(0).get("id"));
String tabname = params.getString("name");
List<Map<String, Object>> data=queryCodeCount(resourceCatalogId);
/**
* 以下为生成Excel操作
*/
// 1.创建一个workbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 2.在workbook中添加一个sheet，对应Excel中的一个sheet
HSSFSheet sheet = wb.createSheet(tabname);
sheet.setDefaultColumnWidth((short) 15);//设置表格的宽度
// 3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short
//第一行
HSSFRow row1 = sheet.createRow((int) 0);
row1.setHeightInPoints(25);//设置表格一行的高度
//第二行
/* HSSFRow row2 = sheet.createRow((int) 1);
row2.setHeightInPoints(20);*/
//正文
HSSFRow row = sheet.createRow((int) 1); //第二行注释掉 so这行为第二行
row.setHeightInPoints(20);//设置表格一行的高度
// 4.创建单元格，设置值表头，设置表头居中
HSSFCellStyle style1 = wb.createCellStyle();
// 居中格式
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
// style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
// style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
// 生成一个字体
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
// 把字体应用到当前的样式
style1.setFont(font);
HSSFCellStyle style = wb.createCellStyle();
// 居中格式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
//solid 填充 foreground 前景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//合并单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, data.size()-1);
sheet.addMergedRegion(region);
HSSFCellStyle style2 = wb.createCellStyle();
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style2.setFont(font);
HSSFCellStyle style5 = wb.createCellStyle();
style5.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
HSSFCell cell = row.createCell(0);
HSSFCell cell1 = row1.createCell(0);
cell1.setCellValue(tabname); //filetitle
SimpleDateFormat dataFor = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
//日期格式化
DecimalFormat doubleFor = new DecimalFormat("0.00"); //格式化数字
// 设置表头 单元格
cell1.setCellStyle(style1);
/**设置单元格格式为文本格式*/
// HSSFCellStyle textStyle = wb.createCellStyle();
// HSSFDataFormat format = wb.createDataFormat();
// textStyle.setDataFormat(format.getFormat("@"));
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
//solid 填充 foreground 前景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
// 获取需要验证的列号List<Integer> list =
List<Integer> dateTypeStart = new ArrayList<Integer>();
List<Integer> numTypeStart = new ArrayList<Integer>();
for(int i=0;i<data.size();i++) {
row1.createCell(i+1).setCellStyle(style1);
cell.setCellValue(String.valueOf(data.get(i).get("fieldCn")));
cell.setCellStyle(style);
cell = row.createCell(i+1);
// 在这记录需要验证的开始行和结束行
Map<String, Object> param =data.get(i);
String uiComCode=param.get("uiComCode").toString();
if(uiComCode.equals("date")||uiComCode.equals("time")||uiComCode.equals("datetime")||uiComCode.equals("float")||uiComCode.equals("double")) {
dateTypeStart.add(i);
}else if(uiComCode.equals("number")||uiComCode.equals("int")||uiComCode.equals("Long")) {
numTypeStart.add(i);
}
}
//创建数据验证类
DataValidationHelper helper = sheet.getDataValidationHelper();
// 这个是时间类型的 根据获取的行号和列号进行 验证
for(int n = 0 ; n <dateTypeStart.size() ; n++) {
//设置验证生效的范围
CellRangeAddressList addressList = new CellRangeAddressList(1, 60000, dateTypeStart.get(n), dateTypeStart.get(n));
//设置验证方式
DataValidationConstraint constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,"1900-01-01","2099-12-31","yyyy-MM-dd");
//创建验证对象
DataValidation dataValidation = helper.createValidation(constraint, addressList);
//错误提示信息
dataValidation.createErrorBox("提示","请输入[yyyy-MM-dd]格式日期");
dataValidation.setShowErrorBox(true);
//验证和工作簿绑定
sheet.addValidationData(dataValidation);
}
for(int m = 0 ; m <numTypeStart.size() ; m++) {
//设置验证生效的范围
CellRangeAddressList addressList1 = new CellRangeAddressList(1, 60000, numTypeStart.get(m), numTypeStart.get(m));
//设置验证方式
DataValidationConstraint constraint1 =helper.createDecimalConstraint
(DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(0), String.valueOf(999999999));
//创建验证对象
DataValidation validation = helper.createValidation(constraint1, addressList1);
//错误提示信息
validation.createErrorBox("提示", "请输入【0-999999999】之间的两位小数");
//设置是否显示错误窗口
validation.setShowErrorBox(true);
//验证和工作簿绑定
sheet.addValidationData(validation);
}
// 文本格式
HSSFCellStyle textStyle = wb.createCellStyle();
HSSFDataFormat format = wb.createDataFormat();
textStyle.setDataFormat(format.getFormat("@"));
// 居中格式
textStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 日期格式
HSSFCellStyle dateStyle = wb.createCellStyle();
HSSFDataFormat format1= wb.createDataFormat();
dateStyle.setDataFormat(format1.getFormat("yyyy年m月d日"));
dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
CellStyle cellStyle = wb.createCellStyle();
CreationHelper createHelper = wb.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy h.mm;@"));
// 数值格式
HSSFCellStyle numStyle = wb.createCellStyle();
numStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
numStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle numStyle1 = wb.createCellStyle();
numStyle1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
//--------------
for (int i = 0; i < 60000; i++) {
// sheet.addValidationData(dataValidation);
row = sheet.createRow((int) i + 2); //循环遍历 第二行开始
// 创建单元格，设置值
for(int j=0;j<data.size();j++) {
cell = row.createCell(j);//区
Map<String, Object> param =data.get(j);
String uiComCode=param.get("uiComCode").toString();
if(uiComCode.equals("date")||uiComCode.equals("time")||uiComCode.equals("datetime")||uiComCode.equals("float")||uiComCode.equals("double")) {
cell.setCellStyle(dateStyle);//设置单元格格式为"时间"
dateTypeStart.add(j);
}else if(uiComCode.equals("number")||uiComCode.equals("int")||uiComCode.equals("Long")) {
cell.setCellStyle(numStyle);//设置单元格格式为"数值"
numTypeStart.add(j);
}else {
cell.setCellStyle(textStyle);//设置单元格格式为"文本"
}
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
}
String fileName = String.valueOf(tabname);
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="
+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
return null;
}
// 获取列的值
public List<Map<String, Object>> queryCodeCount(String id) {
String sql = null;
if(id!=null && !"".equals(id)){
sql = "select b.* from SYS_MODULE_TABLE a left join SYS_MODULE_TABLE_FIELD b on a.id = b.TABLE_ID "
+ " where b.FIELD_TYPE = 1 and b.FLAG=1 AND a.id ='"+id+"' "
+ " ORDER BY b.catalog_item_code asc ";
}
return this.sqlQuery.getJDBCDao().queryToMapListByMapParam(sql, null);
}
// 根据目录id获取表id
public List<Map<String, Object>> selectByid(String id) {
String sql = null;
if(id!=null && !"".equals(id)){
sql = "select id from SYS_MODULE_TABLE where table_name=(select TABLE_NAME from SYS_MODULE_TABLE WHERE RESOURCE_CATALOG_ID='"+id+"')";
}
return this.sqlQuery.getJDBCDao().queryToMapListByMapParam(sql, null);
}
}
1800
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
