1.概念
2.poi的基本使用
api：https://poi.apache.org/
依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
基本使用
public class Demo2 {
@Test
public void createExcel( ) throws Exception {
//定义一个工作薄
HSSFWorkbook hwb = new HSSFWorkbook();
FileOutputStream fos = new FileOutputStream("H:/poi/poi练习1.xls");
//需要创建一个sheet，否则会打不开
HSSFSheet st = hwb.createSheet("sheet1");
st.addMergedRegion(new CellRangeAddress(
1, // 起始行
2, // 结束行
1, // 起始列
2 // 结束列
));
//创建一行
HSSFRow row = st.createRow(0);
//设置行高
row.setHeightInPoints(30);
//创建单元格
HSSFCell cell = row.createCell(0);
//为单元格设置值，什么类型的值都可以
cell.setCellValue("张三");//
表示换行
//添加类型格式转换
HSSFCreationHelper creationHelper = hwb.getCreationHelper();
HSSFCellStyle cellStyle = hwb.createCellStyle();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd"));
// 底部边框,其他边框类似
cellStyle.setBorderBottom(BorderStyle.DASH_DOT);
// 底部边框颜色，其他边框类似
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 背景色
cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
// 前景色
cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
//创建单元格
cell = row.createCell(1);
//为单元格设置值，什么类型的值都可以
cell.setCellValue(new Date());
//添加单元格样式
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
hwb.write(fos);
fos.flush();
fos.close();
}
@Test//导入excel
public void importExcel() throws Exception {
FileInputStream fis = new FileInputStream("H:/poi/poi练习1.xls");
POIFSFileSystem pfs = new POIFSFileSystem(fis);
HSSFWorkbook wb = new HSSFWorkbook(pfs);
HSSFSheet sheet = wb.getSheetAt(0);
if(sheet==null){
return;
}
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
HSSFRow row = sheet.getRow(i);
if(row==null){
continue;
}
for (int j = 0; j < row.getLastCellNum(); j++) {
HSSFCell cell = row.getCell(j);
System.out.println(getVlue(cell));
}
}
fis.close();
}
@Test//转换为文本显示
public void excelToText() throws Exception {
FileInputStream fis = new FileInputStream("H:/poi/poi练习1.xls");
POIFSFileSystem pfs = new POIFSFileSystem(fis);
HSSFWorkbook wb = new HSSFWorkbook(pfs);
ExcelExtractor excelExtractor=new ExcelExtractor(wb);
// 不需要Sheet页的名字
excelExtractor.setIncludeSheetNames(false);
//转换为文本输出
System.out.println(excelExtractor.getText());
fis.close();
}
//判断日期格式并转换
public String getVlue(HSSFCell cell) {
if (cell.getCellType() == CellType.BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
}
if (cell.getCellType() == CellType.NUMERIC) {
//判断是否为日期，假如为日期，那么转换成相应的格式输出，日期是NUMERIC下的一种
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
String format = sdf.format(date);
return format;
} else {
return String.valueOf(cell.getNumericCellValue());
}
} else {
return String.valueOf(cell.getStringCellValue());
}
}
//单元格的对其方式
private static void createCell(Workbook wb, Row row, short column){
Cell cell=row.createCell(column); // 创建单元格
cell.setCellValue(new HSSFRichTextString("value")); // 设置值
CellStyle cellStyle=wb.createCellStyle(); // 创建单元格样式、
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置单元格水平方向对其方式
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置单元格垂直方向对其方式
cell.setCellStyle(cellStyle); // 设置单元格样式
}
@Test
//导入模板，然后按照模板输出
public static void main(String[] args) throws Exception{
InputStream is=new FileInputStream("H:/poi/poi练习1.xls");
POIFSFileSystem fs=new POIFSFileSystem(is);
Workbook wb=new HSSFWorkbook(fs);
// 获取第一个Sheet页，前提不为空
Sheet sheet=wb.getSheetAt(0);
// 获取第一行，前提不为空
Row row=sheet.getRow(0);
// 获取单元格，前提不为空
Cell cell=row.getCell(0);
cell.setCellValue("测试单元格");
FileOutputStream fos=new FileOutputStream("H:/poi/poi练习2.xls");
wb.write(fos);
fos.close();
}
}
工具类待续
3.JFreeChart
3.1 BarChart
api http://developer.51cto.com/art/201112/309132.html
http://www.jfree.org/jfreechart/api/javadoc/index.html
依赖
<dependency>
<groupId>org.jfree</groupId>
<artifactId>jfreechart</artifactId>
<version>1.5.0</version>
</dependency>
demo类
@WebServlet("/barChart")
public class Demo extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req,resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//跨域
resp.setHeader("Access-Control-Allow-Origin", "*");
resp.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE");
resp.setHeader("Access-Control-Max-Age", "3600");
resp.setHeader("Access-Control-Allow-Headers", "x-requested-with");
resp.setContentType(" text/json; charset=utf-8");
HttpSession session = req.getSession();
String barChart=null;
try {
barChart = genLineChart(session);
} catch (Exception e) {
e.printStackTrace();
}
JSONObject json = new JSONObject();
json = json.put("name", barChart);
resp.getWriter().println(json.toString());
}
//返回的是存放后图片的名称
public String getBarChart(HttpSession session) throws Exception {
DefaultCategoryDataset dsd = new DefaultCategoryDataset();
dsd.addValue(510, "深圳", "苹果");
dsd.addValue(320, "深圳", "香蕉");
dsd.addValue(580, "深圳", "橘子");
dsd.addValue(390, "深圳", "梨子");
//解决乱码问题
StandardChartTheme standardChartTheme = new StandardChartTheme("demo");
standardChartTheme.setExtraLargeFont(new Font("隶书", Font.BOLD, 20));
standardChartTheme.setRegularFont(new Font("宋书", Font.PLAIN, 15));
standardChartTheme.setLargeFont(new Font("宋书", Font.PLAIN, 15));
ChartFactory.setChartTheme(standardChartTheme);
JFreeChart chart= ChartFactory.createBarChart
("水果销售情况", "水果", "销售", dsd);
CategoryPlot plot=chart.getCategoryPlot();
// 设置网格背景颜色
plot.setBackgroundPaint(Color.white);
// 设置网格竖线颜色
plot.setDomainGridlinePaint(Color.pink);
// 设置网格横线颜色
plot.setRangeGridlinePaint(Color.pink);
// 显示每个柱的数值，并修改该数值的字体属性
BarRenderer renderer=new BarRenderer();
renderer.setDefaultItemLabelGenerator(new StandardCategoryItemLabelGenerator());
renderer.setDefaultItemLabelsVisible(true);
renderer.setDefaultPositiveItemLabelPosition(new ItemLabelPosition(ItemLabelAnchor.OUTSIDE12, TextAnchor.BASELINE_LEFT));
renderer.setItemLabelAnchorOffset(10D);
// 设置平行柱的之间距离
renderer.setItemMargin(0.4);
plot.setRenderer(renderer);
String name = ServletUtilities.saveChartAsJPEG(chart, 500, 500, session);//改了源代码
System.out.println(name);
return name;
}
//getPieChart
public String getPieChart(HttpSession session) throws Exception {
DefaultPieDataset pieDataset = new DefaultPieDataset();
pieDataset.setValue("苹果", 190);
pieDataset.setValue("橘子", 200);
pieDataset.setValue("香蕉", 600);
StandardChartTheme standardChartTheme = new StandardChartTheme("demo");
standardChartTheme.setExtraLargeFont(new Font("隶书", Font.BOLD, 20));
standardChartTheme.setRegularFont(new Font("宋书", Font.PLAIN, 15));
standardChartTheme.setLargeFont(new Font("宋书", Font.PLAIN, 15));
ChartFactory.setChartTheme(standardChartTheme);
JFreeChart chart = ChartFactory.createPieChart("水果销售情况", pieDataset);
String name = ServletUtilities.saveChartAsJPEG(chart, 500, 500, session);//改了源代码地址
System.out.println(name);
return name;
}
//genLineChart
public String genLineChart(HttpSession session) throws Exception {
TimeSeries timeSeries=new TimeSeries("timeSeries");
timeSeries.add(new Month(1,2018), 100);
timeSeries.add(new Month(2,2018), 200);
timeSeries.add(new Month(3,2018), 300);
timeSeries.add(new Month(4,2018), 400);
timeSeries.add(new Month(5,2018), 560);
timeSeries.add(new Month(6,2018), 600);
timeSeries.add(new Month(7,2018), 750);
timeSeries.add(new Month(8,2018), 890);
timeSeries.add(new Month(9,2018), 120);
timeSeries.add(new Month(10,2018), 400);
timeSeries.add(new Month(11,2018), 1200);
timeSeries.add(new Month(12,2018), 1600);
StandardChartTheme standardChartTheme = new StandardChartTheme("demo");
standardChartTheme.setExtraLargeFont(new Font("隶书", Font.BOLD, 20));
standardChartTheme.setRegularFont(new Font("宋书", Font.PLAIN, 15));
standardChartTheme.setLargeFont(new Font("宋书", Font.PLAIN, 15));
ChartFactory.setChartTheme(standardChartTheme);
// 定义时间序列的集合
TimeSeriesCollection lineDataset=new TimeSeriesCollection();
lineDataset.addSeries(timeSeries);
JFreeChart chart=ChartFactory.createTimeSeriesChart("访问量统计时间折线图", "月份", "访问量", lineDataset);
//设置主标题
chart.setTitle(new TextTitle("某网站访问量统计", new Font("宋书", Font.PLAIN, 15)));
//设置子标题
TextTitle subtitle = new TextTitle("2018年度", new Font("宋书", Font.PLAIN, 15));
chart.addSubtitle(subtitle);
String fileName=ServletUtilities.saveChartAsJPEG(chart, 700, 500, session);
System.out.println(fileName);
return fileName;
}
}
重写org.jfree.chart.servlet.ServletUtilities类，解决图片保存的路径问题
public static String saveChartAsJPEG(JFreeChart chart, int width, int height, ChartRenderingInfo info, HttpSession session) throws IOException {
Args.nullNotPermitted(chart, "chart");
//需要修改的代码，修改文件夹产生的路径
String realPath = session.getServletContext().getRealPath("");
realPath=realPath+"files";
System.out.println("realPath:"+realPath);
File tempDir = new File(realPath);
if (!tempDir.exists()) {
tempDir.mkdirs();
}
String prefix = tempFilePrefix;
if (session == null) {
prefix = tempOneTimeFilePrefix;
}
File tempFile = File.createTempFile(prefix, ".jpeg",new File(realPath));
ChartUtils.saveChartAsJPEG(tempFile, chart, width, height, info);
if (session != null) {
registerChartForDeletion(tempFile, session);
}
return tempFile.getName();
}
工具类：待续