《Java知识应用》Java Excel读取(xls、xlsx)和写入
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了《Java知识应用》Java Excel读取(xls、xlsx)和写入,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7551字,纯文字阅读大概需要11分钟。
内容图文
![《Java知识应用》Java Excel读取(xls、xlsx)和写入](/upload/InfoBanner/zyjiaocheng/666/dbab9dd80bec4681a8d1c99859c64deb.jpg)
第一步:
链接: https://pan.baidu.com/s/1emJpl7cpvKZyRMzWccAhJA 提取码: p3a7 复制这段内容后打开百度网盘手机App,操作更方便哦
下载jar包:
Excel写部分:
第二步写代码:(写入Excel)
public class UserData { public UserData(Long id, String username, String password, String phone){ this.id = id; this.password = password; this.phone = phone; this.username = username; } private Long id; private String username; private String password; private String phone; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } }
import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; 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.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.List; public class ExcelUnitl { private static Workbook wb =null; private static Sheet sheet =null; private static Row row =null; public ExcelUnitl(){ //空参构造 } /** * 导出Excel * @param sheetName sheet名称 * @param title 标题 * @param values 内容 * @param wb HSSFWorkbook对象 * @return */ public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values, HSSFWorkbook wb){ // 第一步,创建一个HSSFWorkbook,对应一个Excel文件 if(wb == null){ wb = new HSSFWorkbook(); } // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet(sheetName); // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制 HSSFRow row = sheet.createRow(0); // 第四步,创建单元格,并设置值表头 设置表头居中 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式 //声明列对象 HSSFCell cell = null; //创建标题 for(int i=0;i<title.length;i++){ cell = row.createCell(i); cell.setCellValue(title[i]); cell.setCellStyle(style); } //创建内容 for(int i=0;i<values.length;i++){ row = sheet.createRow(i + 1); for(int j=0;j<values[i].length;j++){ //将内容按顺序赋给对应的列对象 row.createCell(j).setCellValue(values[i][j]); } } return wb; } public void export(List<UserData> list,String filePath){ //excel标题 String[] title = {"用户ID", "用户名称", "用户密码", "用户手机"}; //excel文件名 String fileName = "用户信息表" + System.currentTimeMillis() + ".xls"; //sheet名 String sheetName = "用户信息表"; String [][] content = new String[list.size()][title.length]; for (int i = 0; i < list.size(); i++) { content[i] = new String[title.length]; UserData obj = list.get(i); content[i][0] = obj.getId().toString(); content[i][1] = obj.getUsername(); content[i][2] = obj.getPassword(); content[i][3] = obj.getPhone(); } //创建HSSFWorkbook HSSFWorkbook wb = getHSSFWorkbook(sheetName, title, content, null); OutputStream os = null; try { os = new FileOutputStream(new File(filePath+fileName)); wb.write(os); os.flush(); } catch (Exception e) { e.printStackTrace(); }finally { try { if(os != null){ os.close(); } } catch (IOException e) { e.printStackTrace(); } } } }
第三步测试一下:
import java.util.ArrayList; import java.util.List; public class ExcelTest { public static void main(String[] args) { List<UserData> list = new ArrayList<>(); //数据源 list.add(new UserData(1L,"张三","123","12345678889")); list.add(new UserData(2L,"李四","456","987654321")); ExcelUnitl excelUnitl= new ExcelUnitl(); String filePath = "src\\demo\\knowledgepoints\\excel\\"; //目标文件路径 excelUnitl.export(list,filePath); } }
运行结果:
Excel读部分:
第二步:准备数据
准备两份不同格式的excel文件:
第三步:写代码
import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.Date; import java.util.HashMap; import java.util.Map; public class ExcelUnitl { private static Workbook wb =null; private static Sheet sheet =null; private static Row row =null; /** * 构造函数初始化wb * @param filepath */ public ExcelUnitl(String filepath){ if(filepath==null){ return; } String ext = filepath.substring(filepath.lastIndexOf(".")); try { InputStream is = new FileInputStream(filepath); if(".xls".equals(ext)){ wb = new HSSFWorkbook(is); }else if(".xlsx".equals(ext)){ wb = new XSSFWorkbook(is); } } catch (IOException e) { e.printStackTrace(); } } /** * 读取Excel表格表头的内容 * @return String 表头内容的数组 * @author zengwendong */ public String[] readExcelTitle() { if(wb==null){ throw new RuntimeException("Excel对象为空"); } sheet = wb.getSheetAt(0); row = sheet.getRow(0); // 标题总列数 int colNum = row.getPhysicalNumberOfCells(); System.out.println("colNum:" + colNum); String[] title = new String[colNum]; for (int i = 0; i < colNum; i++) { title[i] = row.getCell(i).getCellFormula(); } return title; } /** * 读取Excel数据内容 * @return Map 包含单元格数据内容的Map对象 * @author zengwendong */ public Map<Integer, Map<Integer,Object>> readExcelContent() { if(wb==null){ throw new RuntimeException("Workbook对象为空!"); } Map<Integer, Map<Integer,Object>> content = new HashMap<>(); sheet = wb.getSheetAt(0); // 得到总行数 int rowNum = sheet.getLastRowNum(); row = sheet.getRow(0); int colNum = row.getPhysicalNumberOfCells(); // 正文内容应该从第二行开始,第一行为表头的标题 for (int i = 1; i <= rowNum; i++) { row = sheet.getRow(i); int j = 0; Map<Integer,Object> cellValue = new HashMap<>(); while (j < colNum) { Object obj = getCellFormatValue(row.getCell(j)); cellValue.put(j, obj); j++; } content.put(i, cellValue); } return content; } /** * 根据Cell类型设置数据 * @param cell * @return * @author zengwendong */ private Object getCellFormatValue(Cell cell) { Object cellvalue = ""; if (cell != null) { // 判断当前Cell的Type switch (cell.getCellType()) { case NUMERIC:{ // 判断当前的cell是否为Date if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); cellvalue = date; } else {// 如果是纯数字 cellvalue = String.valueOf(cell.getNumericCellValue()); } break; } case STRING:// 如果当前Cell的Type为STRING cellvalue = cell.getRichStringCellValue().getString(); break; default:// 默认的Cell值 cellvalue = ""; } } else { cellvalue = ""; } return cellvalue; } }
测试一下:
import java.util.Map; public class ExcelTest { public static void main(String[] args) { String filepath = "src\\demo\\knowledgepoints\\file\\test.xlsx"; ExcelUnitl excelUnitl= new ExcelUnitl(filepath); Map<Integer, Map<Integer,Object>> map = excelUnitl.readExcelContent(); for (int i = 1; i <= map.size(); i++) { System.out.println(map.get(i)); } } }
运行结果:
换个格式也测试一下:
import java.util.Map; public class ExcelTest { public static void main(String[] args) { String filepath = "src\\demo\\knowledgepoints\\file\\test.xls"; ExcelUnitl excelUnitl= new ExcelUnitl(filepath); Map<Integer, Map<Integer,Object>> map = excelUnitl.readExcelContent(); for (int i = 1; i <= map.size(); i++) { System.out.println(map.get(i)); } } }
运行结果:
参考:https://www.cnblogs.com/hhhshct/p/7255915.html ,https://www.cnblogs.com/jike1219/p/11182303.html
内容总结
以上是互联网集市为您收集整理的《Java知识应用》Java Excel读取(xls、xlsx)和写入全部内容,希望文章能够帮你解决《Java知识应用》Java Excel读取(xls、xlsx)和写入所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。