poi生成excel表格数据和解析excel表格数据

最新推荐文章于 2023-11-01 11:07:20 发布

weixin_33797791

最新推荐文章于 2023-11-01 11:07:20 发布

阅读量116

点赞数

文章标签：前端 python java ViewUI

原文链接：https://my.oschina.net/u/660595/blog/855334

版权

2019独角兽企业重金招聘Python工程师标准>>>

1.poi解析excel表格数据

//读取上传的Excel文件
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(myFile));
//读取第一个标签页
HSSFSheet sheet = workbook.getSheetAt(0);
//遍历标签页中的所有行
for (Row row : sheet) {
int rowNum = row.getRowNum();// 行索引,从0开始
if(rowNum == 0){
//标题行，忽略
continue;
}
String id = row.getCell(0).getStringCellValue();
String province = row.getCell(1).getStringCellValue();
String city = row.getCell(2).getStringCellValue();
String district = row.getCell(3).getStringCellValue();
String postcode = row.getCell(4).getStringCellValue();
Region region = new Region(id, province, city, district, postcode, null, null, null);
list.add(region);
}
regionService.saveBatch(list);

2.poi生成excel表格数据

//查询所有的分区数据
List<Subarea> list = subareaService.findAll();
//使用POI将分区数据写到Excel文件中（内存文件）
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个标签页
HSSFSheet sheet = workbook.createSheet("分区数据");
HSSFCellStyle style = wb.createCellStyle();  
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
//创建 标题行
HSSFRow headRow = sheet.createRow(0);
//创建单元格
headRow.createCell(0).setCellValue("分区编号");
headRow.createCell(1).setCellValue("地址关键字");
headRow.createCell(2).setCellValue("省市区");
headRow.createCell(3).setCellValue("地址详细信息");
for (Subarea subarea : list) {
//创建数据行
HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
dataRow.createCell(0).setCellValue(subarea.getId());
dataRow.createCell(1).setCellValue(subarea.getAddresskey());
dataRow.createCell(2).setCellValue(subarea.getRegion().getName());
dataRow.createCell(3).setCellValue(subarea.getPosition());
}
String filename = "分区数据.xls";
String agent = ServletActionContext.getRequest().getHeader("User-Agent");
filename = FileUtils.encodeDownloadFilename(filename, agent);
String contentType = ServletActionContext.getServletContext().getMimeType(filename);
//一个流（输出流）、两个头（服务端响应的头信息）
ServletActionContext.getResponse().setContentType(contentType);
ServletActionContext.getResponse().setHeader("content-disposition", "attachement;filename="+filename);
//使用输出流向客户端写文件
ServletOutputStream out = ServletActionContext.getResponse().getOutputStream();
workbook.write(out);
out.close();

以上是ssh框架中的部分代码：

如果要使用表格的样式：

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("考勤表");
HSSFRow row = sheet.createRow((int) 0);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);

cell.setCellValue("姓名");
cell.setCellStyle(style);

以下是ssm框架中的代码：

/** 
     * 考勤统计导出
     **/  
	public String toExcel(String string,String name){
		System.out.println(name);
		System.out.println(string);
		JSONArray array =JSONArray.fromObject(string);
    	Object[] obj = new Object[array.size()];	
        List<Attendance> list = new ArrayList();  
    	for(int i = 0; i < array.size(); i++){     
    		JSONObject jsonObject = array.getJSONObject(i);   
            Attendance user = new Attendance(
            		(String) jsonObject.get("name"),
            		(Integer) jsonObject.get("day"),
            		(Integer) jsonObject.get("normal"),
            		(Integer) jsonObject.get("late"),
            		(Integer) jsonObject.get("lateMin"),
            		(Integer) jsonObject.get("advance"),
            		(Integer) jsonObject.get("leaveMin"),
            		(Integer) jsonObject.get("absenteeism"),
            		(Integer) jsonObject.get("miss"),
            		(Integer) jsonObject.get("repair"),
            		(Integer) jsonObject.get("out"),
            		(Integer) jsonObject.get("rest"), 
            		(Integer) jsonObject.get("leave"),
            		(Integer) jsonObject.get("travel"),
            		(String) jsonObject.get("workHours"));	
            list.add(user);
	}
    	String url=toExcel(list, name);
		return url;
    	}
	public static long length(String dir){
		File f = new File(dir);
		if(f.exists()&&f.isFile()){
			return f.length();
		}else{
			return 0;
		}
	}
    public static String toExcel(List<Attendance> list,String name){
    	try{
        HSSFWorkbook wb = new HSSFWorkbook();  
        HSSFSheet sheet = wb.createSheet("考勤表");  
        HSSFRow row = sheet.createRow((int) 0);  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        HSSFCell cell = row.createCell((short) 0);  
        cell.setCellValue("姓名");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 1);  
        cell.setCellValue("出勤天数");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 2);  
        cell.setCellValue("正常打卡");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 3);  
        cell.setCellValue("迟到次数");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 4);  
        cell.setCellValue("迟到总分钟数");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 5);  
        cell.setCellValue("早退次数");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 6);  
        cell.setCellValue("早退总分钟数");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 7);  
        cell.setCellValue("旷工次数");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 8);  
        cell.setCellValue("缺卡次数");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 9);  
        cell.setCellValue("补卡次数");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 10);  
        cell.setCellValue("外出打卡次数");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 11);  
        cell.setCellValue("休息次数");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 12);  
        cell.setCellValue("请假次数");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 13);  
        cell.setCellValue("出差次数");  
        cell.setCellStyle(style); 
        cell = row.createCell((short) 14);  
        cell.setCellValue("平均工时");  
        cell.setCellStyle(style); 
   
        for (int i = 0; i < list.size(); i++)  
        {  
            row = sheet.createRow((int) i + 1);  
            Attendance a = (Attendance) list.get(i);  

            row.createCell((short) 0).setCellValue(a.getName());  
            row.createCell((short) 1).setCellValue(a.getDay());  
            row.createCell((short) 2).setCellValue(a.getNormal());  
            row.createCell((short) 3).setCellValue(a.getLate());  
            row.createCell((short) 4).setCellValue(a.getLateMin());  
            row.createCell((short) 5).setCellValue(a.getAdvance());
            row.createCell((short) 6).setCellValue(a.getLateMin());  
            row.createCell((short) 7).setCellValue(a.getAbsenteeism());  
            row.createCell((short) 8).setCellValue(a.getMiss());
            row.createCell((short) 9).setCellValue(a.getRepair());  
            row.createCell((short) 10).setCellValue(a.getOut());  
            row.createCell((short) 11).setCellValue(a.getRest());
            row.createCell((short) 12).setCellValue(a.getLeave());  
            row.createCell((short) 13).setCellValue(a.getTravel());  
            row.createCell((short) 14).setCellValue(a.getWorkHours());
        }  
        	//这里调用咱们六楼的牛逼产品返回一个路径给前端，记得改一下
            //FileOutputStream fout = new FileOutputStream("/usr/local/tomcat8/webapps/hiteamDDBES/upload/"+name+".xls"); 
        	//FileOutputStream fout = new FileOutputStream("D:/workspace/apache-tomcat-8.0.30/webapps/hiteamDDBES/upload/"+name+".xls");  
            String str = Thread.currentThread().getContextClassLoader().getResource("").toString();
            String filePath = str.substring(str.indexOf("/")+1,str.lastIndexOf("/W"))+"/upload/"+name+".xls";
            FileOutputStream fout = new FileOutputStream(filePath);  
            wb.write(fout); 
            fout.close();

代码仅供参考

转载于:https://my.oschina.net/u/660595/blog/855334