java根据数据导出excel文件
public void bugExport(PageSearQueryVo vo, HttpServletResponse response,HttpServletRequest request) {
//获取导出的全部数据
List<BugListExport> bugExportList = bugListService.selectBugExportList(vo);
//创建文档对象
HSSFWorkbook wb = new HSSFWorkbook();
//创建表单
HSSFSheet sheet=wb.createSheet("1");
CreationHelper createHelper = wb.getCreationHelper();
//创建行
HSSFRow row1=sheet.createRow(0);
// 表头样式
CellStyle headerStyle = wb.createCellStyle();
//创建单元格样式 自动换行
HSSFCellStyle cellStyle=wb.createCellStyle();
Font headerFont2 = wb.createFont();
headerFont2.setFontHeightInPoints((short) 10);
//headerFont2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(headerFont2);
cellStyle.setWrapText(true);
//水平居中
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
//垂直居中
headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//设置边框
headerStyle.setBorderTop(CellStyle.BORDER_THIN);
headerStyle.setBorderRight(CellStyle.BORDER_THIN);
headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
//设置颜色
headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 8);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
//超链接字体颜色
CellStyle hlink_style = wb.createCellStyle();
Font hlink_font = wb.createFont();
hlink_font.setUnderline(Font.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
row1.createCell(0).setCellValue("BUG创建人");
row1.getCell(0).setCellStyle(headerStyle);
row1.createCell(1).setCellValue("项目名称");
row1.getCell(1).setCellStyle(headerStyle);
row1.createCell(2).setCellValue("所属模块");
row1.getCell(2).setCellStyle(headerStyle);
row1.createCell(3).setCellValue("版本信息");
row1.getCell(3).setCellStyle(headerStyle);
row1.createCell(4).setCellValue("结束时间");
row1.getCell(4).setCellStyle(headerStyle);
row1.createCell(5).setCellValue("严重程度");
row1.getCell(5).setCellStyle(headerStyle);
row1.createCell(6).setCellValue("优先级");
row1.getCell(6).setCellStyle(headerStyle);
row1.createCell(7).setCellValue("BUG类型");
row1.getCell(7).setCellStyle(headerStyle);
row1.createCell(8).setCellValue("操作系统");
row1.getCell(8).setCellStyle(headerStyle);
row1.createCell(9).setCellValue("浏览器类型");
row1.getCell(9).setCellStyle(headerStyle);
row1.createCell(10).setCellValue("完成状态");
row1.getCell(10).setCellStyle(headerStyle);
row1.createCell(11).setCellValue("BUG标题");
row1.getCell(11).setCellStyle(headerStyle);
row1.createCell(12).setCellValue("BUG备注");
row1.getCell(12).setCellStyle(headerStyle);
row1.createCell(13).setCellValue("创建时间");
row1.getCell(13).setCellStyle(headerStyle);
row1.createCell(14).setCellValue("附件");
row1.getCell(14).setCellStyle(headerStyle);
//设置单元格内容
//单元格自适应
sheet.autoSizeColumn(0);
sheet.setColumnWidth(0,sheet.getColumnWidth(0)*15/10);
sheet.autoSizeColumn(1);
sheet.setColumnWidth(1,sheet.getColumnWidth(1)*30/10);
sheet.autoSizeColumn(2);
sheet.setColumnWidth(2,sheet.getColumnWidth(2)*15/10);
sheet.autoSizeColumn(3);
sheet.setColumnWidth(3,sheet.getColumnWidth(3)*35/10);
sheet.autoSizeColumn(4);
sheet.setColumnWidth(4,sheet.getColumnWidth(4)*40/10);
sheet.autoSizeColumn(5);
sheet.setColumnWidth(5,sheet.getColumnWidth(5)*15/10);
sheet.autoSizeColumn(6);
sheet.setColumnWidth(6,sheet.getColumnWidth(6)*15/10);
sheet.autoSizeColumn(7);
sheet.setColumnWidth(7,sheet.getColumnWidth(7)*15/10);
sheet.autoSizeColumn(8);
sheet.setColumnWidth(8,sheet.getColumnWidth(8)*25/10);
sheet.autoSizeColumn(9);
sheet.setColumnWidth(9,sheet.getColumnWidth(9)*15/10);
sheet.autoSizeColumn(10);
sheet.setColumnWidth(10,sheet.getColumnWidth(10)*15/10);
sheet.autoSizeColumn(11);
sheet.setColumnWidth(11,sheet.getColumnWidth(11)*40/10);
sheet.autoSizeColumn(12);
sheet.setColumnWidth(12,sheet.getColumnWidth(12)*100/10);
sheet.autoSizeColumn(13);
sheet.setColumnWidth(13,sheet.getColumnWidth(13)*40/10);
sheet.autoSizeColumn(14);
sheet.setColumnWidth(14,sheet.getColumnWidth(14)*60/10);
for(int i = 0; i<bugExportList.size();i++){
//创建行
HSSFRow row=sheet.createRow(i+1);
row.createCell(0).setCellValue(bugExportList.get(i).getCreateName());
row.getCell(0).setCellStyle(cellStyle);
row.createCell(1).setCellValue(bugExportList.get(i).getProjectName());
row.getCell(1).setCellStyle(cellStyle);
row.createCell(2).setCellValue(bugExportList.get(i).getModuleName());
row.getCell(2).setCellStyle(cellStyle);
row.createCell(3).setCellValue(bugExportList.get(i).getVersionName());
row.getCell(3).setCellStyle(cellStyle);
row.createCell(4).setCellValue(bugExportList.get(i).getEndTime());
row.getCell(4).setCellStyle(cellStyle);
row.createCell(5).setCellValue(bugExportList.get(i).getSeverityName());
row.getCell(5).setCellStyle(cellStyle);
row.createCell(6).setCellValue(bugExportList.get(i).getPrioityName());
row.getCell(6).setCellStyle(cellStyle);
row.createCell(7).setCellValue(bugExportList.get(i).getStutusName());
row.getCell(7).setCellStyle(cellStyle);
row.createCell(8).setCellValue(bugExportList.get(i).getOperatingName());
row.getCell(8).setCellStyle(cellStyle);
row.createCell(9).setCellValue(bugExportList.get(i).getBrowserName());
row.getCell(9).setCellStyle(cellStyle);
row.createCell(10).setCellValue(bugExportList.get(i).getStutusName());
row.getCell(10).setCellStyle(cellStyle);
row.createCell(11).setCellValue(bugExportList.get(i).getBugTitlel());
row.getCell(11).setCellStyle(cellStyle);
row.createCell(12).setCellValue(bugExportList.get(i).getBugRemark());
row.getCell(12).setCellStyle(cellStyle);
row.createCell(13).setCellValue(bugExportList.get(i).getCreateTime());
row.getCell(13).setCellStyle(cellStyle);
row.createCell(14).setCellValue("附件<点击查看详情>");
Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
//获取Ip
String ip = ApplicationUtil.ipAdress();
//获取端口号
String port = String.valueOf(request.getServerPort());
String id = "details";
String adress ="http://"+ip+":"+port+"/OA/bug/selectById?bugId="+bugExportList.get(i).getBugId()+"&Id="+id+"&types=1";
link.setAddress(adress);
row.getCell(14).setHyperlink(link);
row.getCell(14).setCellStyle(hlink_style);
}
String fileName=FileUtil.excelExportName("testCase");
//输出Excel文件
OutputStream output;
try {
output = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename="+fileName);
response.setContentType("application/msexcel");
wb.write(output);
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}
根据excel文件把excel数据导入到数据库中
package com.wenzheng.gzw.utils;
import com.wenzheng.gzw.assets.persistence.entity.PmGzwProductionSecurityAccount;
import lombok.extern.slf4j.Slf4j;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.List;
@Slf4j
public class ExcleUtil {
public static void main(String[] args) {
String path = "D:\\2.xlsx";
readExcel2(path, "");
}
/**
* 解析excel 后缀.xls 文件
* @param path
* @param strURL
*/
public static void readExcel1(String path,String strURL) {
List<PmGzwProductionSecurityAccount> list = new ArrayList<>();
try {
// 解析路径的file文件
Workbook workbook = Workbook.getWorkbook(new File(path));
// 获取第一张工作表
Sheet sheet = workbook.getSheet(0);
// 循环获取每一行数据 因为默认第一行为标题行,我们可以从 1 开始循环,如果需要读取标题行,从 0 开始
// sheet.getRows() 获取总行数
for (int i = 1; i < sheet.getRows(); i++) {
PmGzwProductionSecurityAccount pmGzwProductionSecurityAccount = new PmGzwProductionSecurityAccount();
// 获取第二列的第 i 行信息
pmGzwProductionSecurityAccount.setHiddenDangerClass(sheet.getCell(1,i).getContents());
pmGzwProductionSecurityAccount.setDescribe( sheet.getCell(2, i).getContents());
pmGzwProductionSecurityAccount.setDdegreeOfLoss(sheet.getCell(3, i).getContents());
pmGzwProductionSecurityAccount.setPossibility(sheet.getCell(4, i).getContents());
pmGzwProductionSecurityAccount.setHiddenClass( sheet.getCell(5, i).getContents());
pmGzwProductionSecurityAccount.setPersonCharge(sheet.getCell(6, i).getContents());
pmGzwProductionSecurityAccount.setQueryFrequebcy( sheet.getCell(7, i).getContents());
pmGzwProductionSecurityAccount.setRiskHandle(sheet.getCell(8, i).getContents());
pmGzwProductionSecurityAccount.setProgrammeDes(sheet.getCell(9, i).getContents());
list.add(pmGzwProductionSecurityAccount);
}
System.out.println(list);
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
}
/**
* 解析excel 后缀 .xlsx文件
* @param path
* @param strURL
*/
public static void readExcel2(String path,String strURL) {
List<PmGzwProductionSecurityAccount> list = new ArrayList<>();
try {
// 解析路径的file文件
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(path));
// 获取第一张工作表
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
// 循环获取每一行数据 因为默认第一行为标题行,我们可以从 1 开始循环,如果需要读取标题行,从 0 开始
// sheet.getRows() 获取总行数
for (int i = 1; i < sheet.getLastRowNum(); i++) {
PmGzwProductionSecurityAccount pmGzwProductionSecurityAccount = new PmGzwProductionSecurityAccount();
// 获取第二列的第 i 行信息
pmGzwProductionSecurityAccount.setHiddenDangerClass(sheet.getRow(i).getCell(1).toString());
pmGzwProductionSecurityAccount.setDescribe( sheet.getRow(i).getCell(2).toString());
pmGzwProductionSecurityAccount.setDdegreeOfLoss(sheet.getRow(i).getCell(3).toString());
pmGzwProductionSecurityAccount.setPossibility(sheet.getRow(i).getCell(4).toString());
pmGzwProductionSecurityAccount.setHiddenClass( sheet.getRow(i).getCell(5).toString());
pmGzwProductionSecurityAccount.setPersonCharge(sheet.getRow(i).getCell(6).toString());
pmGzwProductionSecurityAccount.setQueryFrequebcy( sheet.getRow(i).getCell(7).toString());
pmGzwProductionSecurityAccount.setRiskHandle(sheet.getRow(i).getCell(8).toString());
pmGzwProductionSecurityAccount.setProgrammeDes(sheet.getRow(i).getCell(9).toString());
list.add(pmGzwProductionSecurityAccount);
}
System.out.println(list.size());
} catch (IOException e) {
e.printStackTrace();
}
}
}
文件格式