html导出excel数据
1、controller
/**
* 导出数据
* @return
* @throws IOException
*/
@GetMapping(value = "group/exportGroup")
public ResponseEntity<byte[]> exportGroup() throws IOException {
Workbook workbook = groupService.exportGroup();
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.write(os);
byte[] bytes = os.toByteArray();
HttpHeaders headers = new HttpHeaders();
//下载显示的文件名，解决中文名称乱码问题
String fileName = new String("团体信息.xlsx".getBytes("UTF-8"),"iso-8859-1");
headers.setContentDispositionFormData("attachment", fileName);
return new ResponseEntity<>(bytes, headers, HttpStatus.CREATED);
}
2、service
@Override
public Workbook exportGroup() {
List<Group> groups = groupMapper.findGroups();
XSSFWorkbook workbook = new XSSFWorkbook();
String[] titles = new String[]{"编号","团体名称","简称","代表色","成立时间","经纪公司","定位城市"};
XSSFSheet sheet = ExcelUtils.getSheet(workbook, "团体信息", titles);
//新增数据行，并且设置单元格数据
int rowNum = 1;
for(Group group : groups){
XSSFRow row = sheet.createRow(rowNum);
row.createCell(0).setCellValue(group.getId());
row.createCell(1).setCellValue(group.getName());
row.createCell(2).setCellValue(group.getAbbreviation());
row.createCell(3).setCellValue(group.getColor());
String dateStr = CommonUtils.dateToString(group.getEstablishedTime(), "yyy-MM-dd");
row.createCell(4).setCellValue(dateStr);
row.createCell(5).setCellValue(group.getEconomicCompany());
row.createCell(6).setCellValue(group.getCity());
rowNum++;
}
return workbook;
}
3、工具包
@Slf4j
public class ExcelUtils<T> {
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private String bDate;
private int year;
/** excel总行数 */
private Integer lineNumber= 0;
/** 成功导入个数 */
private Integer caseCount= 0;
public Integer getLineNumber() {
return lineNumber;
}
public Integer getCaseCount() {
return caseCount;
}
public void addCaseCount() {
caseCount++;
}
/**
* 创建一个Sheet
* @param workbook
* @param sheetName
* @param titles
* @return
*/
public static XSSFSheet getSheet(XSSFWorkbook workbook, String sheetName, String... titles){
XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sheetName);
createTitle(workbook, sheet, titles);
return sheet;
}
/***
* 创建表头
* @param workbook
* @param sheet
*/
private static void createTitle(XSSFWorkbook workbook, XSSFSheet sheet, String... titles) {
//创建表头
XSSFRow row = sheet.createRow(0);
//设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
sheet.setColumnWidth(2, 12 * 256);
sheet.setColumnWidth(3, 17 * 256);
sheet.setColumnWidth(4, 17 * 256);
sheet.setColumnWidth(5, 30 * 256);
//设置为居中加粗
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
font.setBold(true);
style.setAlignment(HorizontalAlignment.CENTER);
style.setFont(font);
XSSFCell cell;
for(int i=0; i<titles.length; i++){
cell = row.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(style);
}
}
public static void main(String[] args) {
String name = "secfsefse.xls";
System.out.println();
}
/**
* excel文件转java对象
* @param is excel输出流
* @param fileName 文件名
* @param excelMapper 对象映射对象
* @param args 需要获取的行数
* @throws IOException
*/
public void excelToObject(InputStream is, String fileName, ExcelDeal excelMapper, int... args) throws IOException, ServiceException {
Workbook workbook = null;
// XSSFWorkbook
if(".xls".equals(fileName.substring(fileName.lastIndexOf(".")))){
workbook = new HSSFWorkbook(is);
}else {
workbook = new XSSFWorkbook(is);
}
if (workbook == null){
log.info("上传文件格式有误！");
return;
}
//遍历xlsx中的sheet
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
// XSSFSheet sheet = workbook.getSheetAt(numSheet);
Sheet sheet = workbook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
// 对于每个sheet，读取其中的每一行
lineNumber = sheet.getLastRowNum();
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
List<String> curarr=new ArrayList<>();
for(int columnNum = 0; columnNum<args.length ; columnNum++){
Cell cell = row.getCell(args[columnNum]);
curarr.add(trimStr(getValue(cell)));
}
excelMapper.rowDealWith(curarr);
}
}
}
/**
* 判断后缀为xlsx的excel文件的数据类
* @param row
* @return
*/
@SuppressWarnings("deprecation")
public static String getValue(Cell row) {
if(row==null){
return "---";
}
if (row.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(row.getBooleanCellValue());
} else if (row.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
double cur=row.getNumericCellValue();
long longVal = Math.round(cur);
Object inputValue = null;
if(Double.parseDouble(longVal + ".0") == cur){
inputValue = longVal;
} else {
inputValue = cur;
}
return String.valueOf(inputValue);
} else if(row.getCellType() == XSSFCell.CELL_TYPE_BLANK || row.getCellType() == XSSFCell.CELL_TYPE_ERROR){
return "---";
}
else {
return String.valueOf(row.getStringCellValue());
}
}
/**
* 判断后缀为xls的excel文件的数据类型
* @param hssfCell
* @return
*/
@SuppressWarnings("deprecation")
public static String getValue(HSSFCell hssfCell) {
if(hssfCell==null){
return "---";
}
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
double cur=hssfCell.getNumericCellValue();
long longVal = Math.round(cur);
Object inputValue = null;
if(Double.parseDouble(longVal + ".0") == cur)
inputValue = longVal;
else
inputValue = cur;
return String.valueOf(inputValue);
} else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_BLANK || hssfCell.getCellType() == hssfCell.CELL_TYPE_ERROR){
return "---";
}
else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
/**
* 字符串修剪 去除所有空白符号 ， 问号 ， 中文空格
* @param str
* @return
*/
public static String trimStr(String str){
if(str==null){
return null;
}
return str.replaceAll("[\\s\\?]", "").replace(" ", "");
}
}