java：Echarts，POI
1.Echarts:
demo.js:
function demo(selector){ var myEcharts=echarts.init(selector); var option = { title : { text: '某站点用户访问来源', subtext: '纯属虚构', x:'center' }, tooltip : { trigger: 'item', formatter: "{a} <br/>{b} : {c} ({d}%)" }, legend: { orient: 'vertical', left: 'left', data: ['直接访问','邮件营销','联盟广告','视频广告','搜索引擎'] }, series : [ { name: '访问来源', type: 'pie', radius : '55%', center: ['50%', '60%'], data:[ {value:335, name:'直接访问'}, {value:310, name:'邮件营销'}, {value:234, name:'联盟广告'}, {value:135, name:'视频广告'}, {value:1548, name:'搜索引擎'} ], itemStyle: { emphasis: { shadowBlur: 10, shadowOffsetX: 0, shadowColor: 'rgba(0, 0, 0, 0.5)' } } } ] }; myEcharts.setOption(option); }
echarts.html:
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"></meta> <title></title> <script type="text/javascript" src="echarts.common.min.js"></script> <script type="text/javascript" src="demo.js"></script> <script type="text/javascript"> function test(){ demo(document.getElementById("d")); } </script> </head> <body> <input type="button" id="test" value="测试" onclick="test()"/> <div id="d" style="border:1px; height:500px; width:500px"></div> </body> </html>
2.POI:
POIDemo1.java:
package com.zzsxt.lee.poi.demo; import java.io.File; import java.io.FileOutputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * @description 写入并创建xls文档 * @author Seven Lee * @date 2017年11月7日 下午1:41:25 * */ public class POIDemo1 { public static void main(String[] args) { // 指定目标路径和文件名 // windows的路径和linux以及mac的路径是不一样 // C:\\test\test.xls // 一般情况下可以使用File.separator-->\,/ File file = new File("D:/123/kuang.xls"); try { // 创建输出流(写入本地磁盘) OutputStream outputStream = new FileOutputStream(file); /** * poi提供 */ // 在目标路径创建目标名称的excel文件 // HSSFWorkbook:操作excel表格的 // 通过file的路径信息和文件名称，在目标路径创建出目标名称的excel文件 HSSFWorkbook workBook = new HSSFWorkbook(); // 创建excel工作表，并创建该工作页的主题名 // 创建表格的一个工作薄，并起名字为user HSSFSheet sheet = workBook.createSheet("user"); // 从数据库中查询出了数据List<> List<User> userList = new ArrayList<User>(); for (int j = 0; j < 10; j++) { User user = new User("zhangsan" + j, "123456" + j); userList.add(user); } User user = new User(); for (int i = -1; i < userList.size(); i++) { /** * 第一次for循环: * i--> -1 * 第二次循环: * i--> 0 */ // sheet.createRow(i+1)：创建第一行 // sheet.createRow(i+1)：创建第二行 HSSFRow row = sheet.createRow(i+1); for(int k = 0; k < user.getClass().getDeclaredFields().length; k++) { if(i == -1) { if(k == 0) { continue; } row.createCell(k-1).setCellValue((user.getClass().getDeclaredFields()[k]).getName()); continue; } if(((user.getClass().getDeclaredFields()[k]).getName()).equals("serialVersionUID")) { continue; } Field f = userList.get(i).getClass().getDeclaredFields()[k]; f.setAccessible(true); row.createCell(k-1).setCellValue((String)f.get(userList.get(i))); } } // // 为工作表新增加一行(第一行) // HSSFRow row1 = sheet.createRow(0); // // 在指定的行上增加两个单元格 // row1.createCell(0).setCellValue("name"); // row1.createCell(1).setCellValue("password"); // // // 为工作表新增加一行(第二行) // HSSFRow row2 = sheet.createRow(1); // row2.createCell(0).setCellValue("zhangsan"); // row2.createCell(1).setCellValue("123456"); // 调用输出流把excel表格写入硬盘中 workBook.write(outputStream); // 关闭输出流 outputStream.close(); System.out.println("生成excel成功"); } catch (Exception e) { e.printStackTrace(); } } }
POIDemo2.java:
package com.zzsxt.lee.poi.demo; import java.io.BufferedInputStream; import java.io.FileInputStream; import java.io.InputStream; import java.lang.reflect.Field; 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.poifs.filesystem.POIFSFileSystem; /** * @description 通过java程序来从xls文档中读取出数据 * @author Seven Lee * @date 2017年11月7日 下午3:50:18 * */ public class POIDemo2 { public static void main(String[] args) { try { // 创建输入流，从磁盘中获取该文件的流信息 InputStream inputStream = new FileInputStream("D:/123/kuang.xls"); /** * poi提供 */ // 把从磁盘中读取出的流信息保存进缓冲区 BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream); // 创建POIFSFileSystem，对该文件进行解析 // 对该文件的流进行解析 POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream); // 从目标路径的目标名称获取excel的文件信息-->通过fileSystem进行获取 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileSystem); // 根据工作薄的name获取到目标的excel信息 HSSFSheet sheet = hssfWorkbook.getSheet("user"); User user = new User(); for (int i = 0;; i++) { if (i == 0) { continue; } HSSFRow row = sheet.getRow(i);// 获取行信息 if (row != null) { // 需要把所有的数据从excel中取出来 // getLastCellNum():excel表中有效单元格的列数 for (int j = 0; j < row.getLastCellNum(); j++) { String excelValue = row.getCell(j).getStringCellValue(); Field[] fields = user.getClass().getDeclaredFields(); fields[j].setAccessible(true); fields[j].set(user, excelValue); } bufferedInputStream.close(); } else { bufferedInputStream.close(); return; } System.out.println(user); } } catch (Exception e) { e.printStackTrace(); } } }
ReadDocFile.java:
package com.zzsxt.lee.poi.demo; import java.io.FileInputStream; import java.io.FileNotFoundException; import org.apache.poi.hwpf.extractor.WordExtractor; public class ReadDocFile { public static String readWord(String pathAndName) { FileInputStream in; String text = null; try { in = new FileInputStream(pathAndName); WordExtractor extractor = new WordExtractor(in); text = extractor.getText(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } return text; } }
User.java:
package com.zzsxt.lee.poi.demo; import java.io.Serializable; public class User implements Serializable { private static final long serialVersionUID = 5157419433803033003L; private String name; private String password; public User() { } public User(String name, String password) { this.name = name; this.password = password; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return "User [name=" + name + ", password=" + password + "]"; } }
pom.xml:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.zzsxt.lee.poi.demo</groupId> <artifactId>excel_demo</artifactId> <packaging>war</packaging> <version>0.0.1-SNAPSHOT</version> <name>excel_demo Maven Webapp</name> <url>http://maven.apache.org</url> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <!-- 操作excel --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <!-- 操作work和ppt --> <dependency> <groupId>poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.1-FINAL</version> </dependency> </dependencies> <build> <finalName>excel_demo</finalName> </build> </project>
POIDemo3：创建excel 2007 xls格式
package com.zskj; import java.io.FileOutputStream; import java.io.IOException; import java.util.HashSet; import java.util.Iterator; import java.util.Set; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; 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.ss.usermodel.Font; public class Test { public static void main(String[] args) { Set <String> set = new HashSet<String>(); set.add("123"); set.add("456"); set.add("789"); set.add("666"); set.add("777"); createExcel("D:\\test\\", "aa.xls","测试",set); } /** * * @param path 文件存放路径 * @param fileName 文件名称 * @param sheetName 表名称 * @param set 数据 */ public static void createExcel(String path, String fileName,String sheetName,Set<String> set) { // 第一步创建workbook HSSFWorkbook wb = new HSSFWorkbook(); // 第二步创建sheet HSSFSheet sheet = wb.createSheet(sheetName); // 第三步创建行row:添加表头0行 HSSFRow row = sheet.createRow(0); HSSFCellStyle style = wb.createCellStyle(); HSSFFont hssfFont = wb.createFont(); // 设置字体颜色 //hssfFont.setColor(HSSFColor.BLUE.index); //字体大小 hssfFont.setFontHeightInPoints((short)11); //字体 hssfFont.setFontName("宋体"); //粗体 hssfFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(hssfFont); //居中 // style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 第四步创建单元格 HSSFCell cell = row.createCell(0); // 第一个单元格 cell.setCellValue("姓名"); cell.setCellStyle(style); cell = row.createCell(1); // 第二个单元格 cell.setCellValue("年龄"); cell.setCellStyle(style); // 第五步插入数据 Iterator<String> is = set.iterator(); int count = 1; while(is.hasNext()){
//此处注意分开写 row = sheet.createRow(count); HSSFCell hc = orow.createCell(0);
hc.setCellValue(is.next()); count++; } // 第六步将生成excel文件保存到指定路径下 try { FileOutputStream fout = new FileOutputStream(path + fileName); wb.write(fout); fout.close(); } catch (IOException e) { e.printStackTrace(); } System.out.println("Excel文件生成成功..."); } }
POIDemo4：创建excel xlsx格式：
import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.HashSet; import java.util.Iterator; import java.util.Set; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @param path 文件存放路径 * @param fileName 文件名称 * @param sheetName 表名称 * @param set 数据 */ public static void createExcel(String fileName,String path,Set<String>set,String sheetName){ //创建工作簿 XSSFWorkbook xw = new XSSFWorkbook(); //创建表 XSSFSheet xf = xw.createSheet(sheetName); //创建字体 XSSFFont xft = xw.createFont(); //设置字体 xft.setFontName("微软雅黑"); //创建样式 XSSFCellStyle xcs = xw.createCellStyle(); //第一行设置为列名 XSSFRow xr = xf.createRow(0); XSSFCell xc = xr.createCell(0); xc.setCellValue("姓名"); xcs.setFont(xft); xc.setCellStyle(xcs); Iterator<String> is = set.iterator(); int count = 1; while(is.hasNext()){ xr = xf.createRow(count); xc = xr.createCell(0); xc.setCellValue(is.next()); xc.setCellStyle(xcs); count++; } //合并单元格 参数说明：1：开始行 2：结束行 3：开始列 4：结束列 xf.addMergedRegion(new CellRangeAddress(count+2,count+2,0,100)); XSSFRow count2 = xf.createRow(count+2); count2.createCell(0).setCellValue("总计"+(count-1)); File f = new File("D:\\test\\fontstyle.xlsx"); try { FileOutputStream fos = new FileOutputStream(f); xw.write(fos); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println("Excel创建完成......"); }
POIDemo4：读取excel xls格式：
import java.io.File; import java.io.FileInputStream; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; // 读取，全部sheet表及数据 public static void showExcel() throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("D:/test/t1.xls"))); HSSFSheet sheet = null; for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表 sheet = workbook.getSheetAt(i); for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {// getLastRowNum，获取最后一行的行标 HSSFRow row = sheet.getRow(j); if (row != null) { for (int k = 0; k < row.getLastCellNum(); k++) {// getLastCellNum，是获取最后一个不为空的列是第几个 if (row.getCell(k) != null) { // getCell 获取单元格数据 System.out.print(row.getCell(k) + "\t"); } else { System.out.print("\t"); } } } System.out.println(""); // 读完一行后换行 } System.out.println("读取sheet表：" + workbook.getSheetName(i) + " 完成"); } } // 读取，指定sheet表及数据 public static void showExcel2() throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("D:/test/t1.xls"))); HSSFSheet sheet = null; int i = workbook.getSheetIndex("xt"); // sheet表名 sheet = workbook.getSheetAt(i); for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {// getLastRowNum // 获取最后一行的行标 HSSFRow row = sheet.getRow(j); if (row != null) { for (int k = 0; k < row.getLastCellNum(); k++) {// getLastCellNum // 是获取最后一个不为空的列是第几个 if (row.getCell(k) != null) { // getCell 获取单元格数据 System.out.print(row.getCell(k) + "\t"); } else { System.out.print("\t"); } } } System.out.println(""); } }
