精华内容
下载资源
问答
  • 利用Python取出excel数据并生成统计图

    千次阅读 2019-09-14 22:07:30
    excel中取出数据,然后生成一统计图表 借助了Python的第三方模块xlrd和pyecharts xlrd Python用来读取excel数据的模块 安装pip install xlrd xlrd文档 pyecharts Echarts 是个由百度开源的数据可视化,...

    取出excel数据生成图表

    • 帮朋友写的一个小脚本
    • 从excel中取出数据,然后生成一个统计图表
    • 借助了Python的第三方模块xlrd和pyecharts

    xlrd

    • Python中用来读取excel数据的模块
    • 安装pip install xlrd
    • xlrd文档

    pyecharts

    • Echarts 是一个由百度开源的数据可视化,凭借着良好的交互性,精巧的图表设计,得到了众多开发者的认可。
    • pyecharts就是通过Python使用Echarts。
    • 安装pip install pyecharts
    • 注意
      • 现在有很多网上的使用pyecharts的版本都是v0.5.x的,现在安装都是v1版本的,两者差别还是蛮大的。
      • pyecharts 分为 v0.5.X 和 v1 两个大版本,v0.5.X 和 v1 间不兼容,v1 是一个全新的版本
      • pyecharts v1版本文档

    代码分析

    # 打开存储数据的excel
    data = xlrd.open_workbook('C:\\Users\\Administrator\\Desktop\\新建文件夹\\个人-月\\12.xlsx')
    
    # 以表格的形式取出数据
    table = data.sheets()[0]
    
    # 取出表格中第一列数据 
    PM2_5 = table.col_values(0)
    
    # 生成一个折线统计图对象
    line = Line()
    # 添加 x 轴元素
    line.add_xaxis(handle_time_list(time[1:]))
    # 添加 y 轴元素
    line.add_yaxis('PM2.5', PM2_5[1:])
    
    # 渲染到html页面
    line.render('./person/12.html')
    
    • 代码很简单,就是表格中的数据较多。
      生成图片的大概样式

    实现代码

    from pyecharts.charts import Bar, Line
    import xlrd
    
    
    def handle_time_list(time_list):
        """
        处理float格式的时间数据为 年/月/日 小时:分钟
        time_list: 时间列表
        return: 处理好的时间数据列表
        """
        new_time_list = [xlrd.xldate_as_datetime(i, 0).strftime(r'%Y/%m/%d %H:%M') for i in time_list]
        return new_time_list
    
    data = xlrd.open_workbook('C:\\Users\\Administrator\\Desktop\\新建文件夹\\个人-月\\12.xlsx')
    table = data.sheets()[0]
    
    PM2_5 = table.col_values(0)
    PM10 = table.col_values(1)
    CO = table.col_values(2)
    NO2 = table.col_values(3)
    SO2 = table.col_values(4)
    O3 = table.col_values(5)
    wind = table.col_values(6)
    pressure = table.col_values(7)
    water = table.col_values(8)
    temperature = table.col_values(9)
    humidity = table.col_values(10)
    time = table.col_values(11)
    
    
    line = Line()
    line.add_xaxis(handle_time_list(time[1:]))
    line.add_yaxis('PM2.5', PM2_5[1:])
    line.add_yaxis('PM10', PM10[1:])
    line.add_yaxis('CO', CO[1:])
    line.add_yaxis('NO2', NO2[1:])
    line.add_yaxis('SO2', SO2[1:])
    line.add_yaxis('O3', O3[1:])
    line.add_yaxis('风速', wind[1:])
    line.add_yaxis('压强', pressure[1:])
    line.add_yaxis('降水量', water[1:])
    line.add_yaxis('温度', temperature[1:])
    line.add_yaxis('湿度', humidity[1:])
    line.render('./person/12.html')
    
    • 想查看excel表格数据大致结构的可以点这里

    总结

    • Python还是很强大的!
    展开全文
  • 使用Apache poi生成excel并绘制折线统计图,扇形统计图功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入段漂亮的代码片生成一个适合你的列表创建个表格设定内容居中、居左...

    使用Apache poi生成excel并绘制折线统计图,扇形统计图

    使用Apache poi生成excel并绘制折线统计图,扇形统计图

    1.Gradle dependencies

    	// https://mvnrepository.com/artifact/org.apache.poi/poi
    	compile group: 'org.apache.poi', name: 'poi', version: '4.0.1'
    	// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
    	compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.0.1'
    	// https://mvnrepository.com/artifact/org.projectlombok/lombok
    	compile group: 'org.projectlombok', name: 'lombok', version: '1.18.4'
    

    2.import

    	import java.awt.Color;
    	import java.io.File;
    	import java.io.FileOutputStream;
    	import java.io.IOException;
    	import java.io.OutputStream;
    	import java.util.HashMap;
    	import java.util.List;
    	import java.util.Map;
    	
    	import org.apache.poi.ss.usermodel.BorderStyle;
    	import org.apache.poi.ss.usermodel.Cell;
    	import org.apache.poi.ss.usermodel.FillPatternType;
    	import org.apache.poi.ss.usermodel.Row;
    	import org.apache.poi.ss.util.CellRangeAddress;
    	import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
    	import org.apache.poi.xddf.usermodel.chart.AxisPosition;
    	import org.apache.poi.xddf.usermodel.chart.ChartTypes;
    	import org.apache.poi.xddf.usermodel.chart.LegendPosition;
    	import org.apache.poi.xddf.usermodel.chart.MarkerStyle;
    	import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
    	import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
    	import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
    	import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
    	import org.apache.poi.xddf.usermodel.chart.XDDFLineChartData;
    	import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
    	import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
    	import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    	import org.apache.poi.xssf.usermodel.XSSFChart;
    	import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
    	import org.apache.poi.xssf.usermodel.XSSFColor;
    	import org.apache.poi.xssf.usermodel.XSSFDrawing;
    	import org.apache.poi.xssf.usermodel.XSSFFont;
    	import org.apache.poi.xssf.usermodel.XSSFSheet;
    	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTPieChart;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
    	import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
    	import org.openxmlformats.schemas.drawingml.x2006.main.CTShapeProperties;
    	import org.springframework.stereotype.Service;
    	
    	import lombok.extern.slf4j.Slf4j;
    

    3.class

    	public class MyExcleChart2{
    	
    	private static Color titleBackColor = new Color(155,194,230);	//表头背景色
    
    	public static void doWork(List<String> title,List<String> styleList, Map<String, List<Object>> day2ColValueList, File file,
    			String sheetName,XSSFWorkbook wb,int dateNum) throws IOException {
    		OutputStream out = null;
    		try{
    			int sheetIndex = wb.getSheetIndex(sheetName);
    			if(sheetIndex >= 0){
    				wb.removeSheetAt(sheetIndex);
    			}
    			int sheetNum = wb.getNumberOfSheets();
    			XSSFSheet sheet = wb.createSheet();
    			wb.setSheetName(sheetNum, sheetName);
        		out = new FileOutputStream(file);    
    
    			//设置内容样式
        		XSSFCellStyle  style = setBorder(wb);
    			
    			//设置表头字体
    			XSSFFont font = wb.createFont();
    			font.setBold(true);	//加粗
    			//设置表头样式
    			XSSFCellStyle headStyle = setBorder(wb);
    			headStyle.setFillForegroundColor(new XSSFColor(titleBackColor));
    			headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    			headStyle.setFont(font);
    			
    			//隐藏列
        		hiddenColumn(sheet,sheetName);
    			
    			Row row;
    			Cell cell = null;
    			row = sheet.createRow(0);
    			//写入表头
    			int titleColIndex=0;
                for(String t:title) {
                	cell = row.createCell((short) titleColIndex);
                	cell.setCellValue(t);
                	cell.setCellStyle(headStyle);
                	titleColIndex++;
                } 
                //写入数据
                int rowIndex = 1;
                for(String key:day2ColValueList.keySet()){
                	row = sheet.createRow(rowIndex);
                	
                	List<Object> dataList = day2ColValueList.get(key);
                	cell = row.createCell(0);
                	cell.setCellStyle(style);
                	cell.setCellValue(rowIndex);
                	
                	int cellIndex = 1;
                	for(Object s : dataList){
                		//填充单元格
                		String cellstyle = styleList.get(dataList.indexOf(s)+1);
                		cell = row.createCell(cellIndex);
                		cell.setCellStyle(style);
                		//此处可以对特殊的行进行处理
                		if("speciaRowName".equals(key)&&cellIndex>9){
                			cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue("speciaRowName");
                			cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("");
                			cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("");
                			cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue("");
                			cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue("");
                			cell = row.createCell(cellIndex); cell.setCellStyle(style); double dble = (double)s; cell.setCellValue(dble);
                		}else if("int".equals(cellstyle)&&null!=s){
                			int num = (int) s;
                			cell.setCellValue(num);
                		}else if("double".equals(cellstyle)&&null!=s){
                			double dble = (double)s;
                			cell.setCellValue(dble);
                		}else{
                			cell.setCellValue(null==s?"":(String)s);
                		}
                		cellIndex ++;
                	}
                	rowIndex ++;
                }
                //绘制图表
                if(day2ColValueList.size()>0){
                	drawChart(sheet, sheetName, day2ColValueList, titleColIndex,dateNum);
                }
                wb.write(out);
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {  
    		    try {
    				out.close();
    			} catch (IOException e) {
    				e.printStackTrace();
    			}
    		}  
    	}
    	
    	private static void drawChart(XSSFSheet sheet, String sheetName, Map<String, List<Object>> day2ColValueList,
    			int titleSize,int dateNum) {
    		Map<String, Integer> paramMap = new HashMap<String, Integer>();// 折线图入参
    		Map<String, Object> pieParamMap = new HashMap<String, Object>();// 扇形图入参
    		//此处可以分sheet处理数据
    		if ("sheetname1".equals(sheetName)) {//sheetname1,需要显示折线图和扇形图
    			paramMap.put("numstartcol", 5);
    			paramMap.put("numendcol", 5+dateNum-1);
    			paramMap.put("prostartcol", 5+dateNum);
    			paramMap.put("proendcol", 5+dateNum+dateNum-1);
    			// 业务汇总表,需要统计身份核查、高清人像、银行卡、手机实名四个业务的折线图
    			for (String key : day2ColValueList.keySet()) {
    				if ("类型1业务1".equals(key)) {// 人像比对认证走势折线图
    					// 折线图x轴单位起止列(numstartcol,numendcol),以及计费笔数数据所在行列
    					paramMap.put("col1", 0);
    					paramMap.put("col2", 7);
    					paramMap.put("row1", day2ColValueList.size()+2);
    					paramMap.put("row2", day2ColValueList.size()+19);
    					paramMap.put("numstartrow", 1);
    					paramMap.put("numendrow", 1);
    					paramMap.put("numstartrow2", 2);
    					paramMap.put("numendrow2", 2);
    					// 折线图净收入所在行列
    					paramMap.put("prostartrow", 1);
    					paramMap.put("proendrow", 1);
    					paramMap.put("prostartrow2", 2);
    					paramMap.put("proendrow2", 2);
    					drawLineChart(sheet, "业务1", paramMap);
    				} else if ("类型1业务2".equals(key)) {// 银行卡认证走势折线图
    					paramMap.put("col1", 8);
    					paramMap.put("col2", 15);
    					paramMap.put("row1", day2ColValueList.size()+2);
    					paramMap.put("row2", day2ColValueList.size()+19);
    					paramMap.put("numstartrow", 3);
    					paramMap.put("numendrow", 3);
    					paramMap.put("numstartrow2", 4);
    					paramMap.put("numendrow2", 4);
    					paramMap.put("prostartrow", 3);
    					paramMap.put("proendrow", 3);
    					paramMap.put("prostartrow2", 4);
    					paramMap.put("proendrow2", 4);
    					drawLineChart(sheet, "业务2", paramMap);
    				} 
    			}
    			//柱状图
    			paramMap.put("col1", 15);
    			paramMap.put("col2", 23);
    			paramMap.put("row1", day2ColValueList.size()+2);
    			paramMap.put("row2", day2ColValueList.size()+19);
    			paramMap.put("numstartrow", 1);
    			paramMap.put("numendrow", 1);
    			paramMap.put("prostartrow", 1);
    			paramMap.put("proendrow", 1);
    //			drawBarChart(sheet, "业务1", paramMap);
    			
    			// 扇形图
    			pieParamMap.put("col1", 0);
    			pieParamMap.put("col2", 7);
    			pieParamMap.put("row1", day2ColValueList.size()+20);
    			pieParamMap.put("row2", day2ColValueList.size()+39);
    			pieParamMap.put("data1", "sheetname1!$C$1");
    			pieParamMap.put("data2", "sheetname1!$C$2:$C$3");
    			pieParamMap.put("data3", "sheetname1!$D$2:$D$3");
    			drawPieChart(sheet, "类型1各业务交易量占比", pieParamMap);
    			pieParamMap.put("col1", 8);
    			pieParamMap.put("col2", 15);
    			pieParamMap.put("data3", "sheetname1!$E$2:$E$3");
    			drawPieChart(sheet, "类型1各业务净收入占比", pieParamMap);
    			pieParamMap.put("col1", 0);
    			pieParamMap.put("col2", 7);
    			pieParamMap.put("row1", day2ColValueList.size()+40);
    			pieParamMap.put("row2", day2ColValueList.size()+59);
    			pieParamMap.put("data2", "sheetname1!$C$4:$C$5");
    			pieParamMap.put("data3", "sheetname1!$D$4:$D$5");
    			drawPieChart(sheet, "类型2各业务交易量占比", pieParamMap);
    			pieParamMap.put("col1", 8);
    			pieParamMap.put("col2", 15);
    			pieParamMap.put("data3", "sheetname1!$e$4:$e$5");
    			drawPieChart(sheet, "类型2各业务净收入占比", pieParamMap);
    		} 
    	}
    
    	/**
    	 * 绘制扇形图
    	 * @param sheet sheet
    	 * @param string 标题
    	 * @param paramMap 各种起始截止行列
    	 * col1 col2 row1 row2 图片坐标
    	 * data1  种类划分标志所在列
    	 * data2  各分类名
    	 * data3  各分类数值
    	 */
    	private static void drawPieChart(XSSFSheet sheet, String title, Map<String, Object> pieParamMap) {
    		int col1 = (int) pieParamMap.get("col1");int col2 = (int) pieParamMap.get("col2");
    		int row1 = (int) pieParamMap.get("row1");int row2 = (int) pieParamMap.get("row2");
    		String data1 = (String) pieParamMap.get("data1");
    		String data2 = (String) pieParamMap.get("data2");
    		String data3 = (String) pieParamMap.get("data3");
    		
    		XSSFDrawing drawing = sheet.createDrawingPatriarch();
    		XSSFClientAnchor anchor = (XSSFClientAnchor) drawing.createAnchor(0, 0, 0, 0, col1, row1, col2, row2);
    		
    		XSSFChart chart = drawing.createChart(anchor);
    		chart.setTitleText(title);
    		chart.setTitleOverlay(false);
    
    		CTChart ctChart = ((XSSFChart) chart).getCTChart();
    		CTPlotArea ctPlotArea = ctChart.getPlotArea();
    		CTPieChart ctPieChart = ctPlotArea.addNewPieChart();
    		CTBoolean ctBoolean = ctPieChart.addNewVaryColors();
    		ctBoolean.setVal(true);
    
    		CTPieSer ctPieSer = ctPieChart.addNewSer();
    		CTSerTx ctSerTx = ctPieSer.addNewTx();
    		CTStrRef ctStrRefTx = ctSerTx.addNewStrRef();
    		ctStrRefTx.setF(data1);
    		ctPieSer.addNewIdx().setVal(0);
    		CTAxDataSource cttAxDataSource = ctPieSer.addNewCat();
    		CTStrRef ctStrRef = cttAxDataSource.addNewStrRef();
    		ctStrRef.setF(data2); // 第一行为标题
    		CTNumDataSource ctNumDataSource = ctPieSer.addNewVal();
    		CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
    		ctNumRef.setF(data3); // 第一行为标题
    
    		ctPieSer.addNewDLbls().addNewShowLeaderLines();// 有无此行代码,图上是否显示文字
    
    		// legend图注
    		CTLegend ctLegend = ctChart.addNewLegend();
    		ctLegend.addNewLegendPos().setVal(STLegendPos.TR);
    		ctLegend.addNewOverlay().setVal(true);
    
    		ctPieSer.addNewExplosion().setVal(1);// 各块之间间隙大小
    		ctPieSer.addNewOrder().setVal(0);//
    
    		CTShapeProperties cTShapeProperties = CTShapeProperties.Factory.newInstance();
    		ctPieSer.addNewSpPr().set(cTShapeProperties);
    	}
    
    	/**
    	 * 绘制折线图
    	 * @param sheet sheet
    	 * @param desc	横轴描述
    	 * @param 各种起始截止行列,包含如下内容:
    	 * 		int numstartrow,int numendrow,int numstartcol,int numendcol,//需要绘图的计费笔数起始截止行列
    			int prostartrow,int proendrow,int prostartcol,int proendcol,//需要绘图的业务净收入起始截止行列
    			int col1,int row1,int row2 //绘图的起始行列
    	 */
    	private static void drawLineChart(XSSFSheet sheet,String desc,Map<String,Integer> paramMap) {
    		int col1 = paramMap.get("col1"),col2 = paramMap.get("col2"), row1=paramMap.get("row1"),row2 = paramMap.get("row2"),//绘图所在坐标,默认宽度为12列
    			//双折线图x轴单位起止列(numstartcol,numendcol),以及第一类数据所在行列	
    			numstartrow = paramMap.get("numstartrow"),numendrow = paramMap.get("numendrow"),numstartcol = paramMap.get("numstartcol"),numendcol = paramMap.get("numendcol"),
    			//双折线图数据2所在行列
    			prostartrow = paramMap.get("prostartrow"),proendrow = paramMap.get("proendrow"),prostartcol = paramMap.get("prostartcol"),proendcol = paramMap.get("proendcol");
    		
    		int dx1=0;
    		int dy1=0;
    		int dx2=0;
    		int dy2=0;
    		XSSFDrawing drawing = sheet.createDrawingPatriarch();
    		XSSFClientAnchor anchor = drawing.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
    
            XSSFChart chart = drawing.createChart(anchor);
            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP_RIGHT);
    
            // Use a category axis for the bottom axis.
            XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.TOP);//底部X轴
            bottomAxis.setTitle(desc+"交易情况汇总"); // https://stackoverflow.com/questions/32010765
            
            XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);//左侧Y轴
            leftAxis.setTitle("交易量/金额");
            leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
            
            XDDFLineChartData leftdata = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
            
            /填充数据
    		CellRangeAddress cellRangeAddress=new CellRangeAddress(0, 0, numstartcol, numendcol);
    		XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet, cellRangeAddress);//日期
    		
    		CellRangeAddress dataCellRangeAddress=new CellRangeAddress(numstartrow, numendrow, numstartcol, numendcol);
    		XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress);//纵轴为各个数据
            XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) leftdata.addSeries(xs, ys1);
            series1.setTitle("交易量总计(笔)", null); // https://stackoverflow.com/questions/21855842
            series1.setSmooth(false); // https://stackoverflow.com/questions/29014848
            series1.setMarkerStyle(MarkerStyle.DASH); // https://stackoverflow.com/questions/39636138
            
            CellRangeAddress dataCellRangeAddress2=new CellRangeAddress(prostartrow, proendrow,prostartcol,proendcol);
            XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress2);//纵轴为各个数据
            XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) leftdata.addSeries(xs, ys2);
            series2.setTitle("收入总计(元)", null); // https://stackoverflow.com/questions/21855842
            series2.setSmooth(false); // https://stackoverflow.com/questions/29014848
            series2.setMarkerStyle(MarkerStyle.DASH); // https://stackoverflow.com/questions/39636138
            
            if(null!=paramMap.get("numstartrow2")&&null!=paramMap.get("numendrow2")&&null!=paramMap.get("prostartrow2")&&null!=paramMap.get("proendrow2")){
    			int numstartrow2 = paramMap.get("numstartrow2"), numendrow2 = paramMap.get("numendrow2"),
    				prostartrow2 = paramMap.get("prostartrow2"), proendrow2 = paramMap.get("proendrow2");
    			series1.setTitle("类型1交易量总计(笔)", null); // https://stackoverflow.com/questions/21855842
    			series2.setTitle("类型1收入总计(元)", null); // https://stackoverflow.com/questions/21855842
    			CellRangeAddress dataCellRangeAddress3=new CellRangeAddress(numstartrow2, numendrow2, numstartcol, numendcol);
    			XDDFNumericalDataSource<Double> ys3 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress3);//纵轴为各个数据
    	        XDDFLineChartData.Series series3 = (XDDFLineChartData.Series) leftdata.addSeries(xs, ys3);
    	        series3.setTitle("类型2交易量总计(笔)", null); // https://stackoverflow.com/questions/21855842
    	        series3.setSmooth(false); // https://stackoverflow.com/questions/29014848
    	        series3.setMarkerStyle(MarkerStyle.DASH); // https://stackoverflow.com/questions/39636138
    	        
    	        CellRangeAddress dataCellRangeAddress4=new CellRangeAddress(prostartrow2, proendrow2,prostartcol,proendcol);
    	        XDDFNumericalDataSource<Double> ys4 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress4);//纵轴为各个数据
    	        XDDFLineChartData.Series series4 = (XDDFLineChartData.Series) leftdata.addSeries(xs, ys4);
    	        series4.setTitle("类型2收入总计(元)", null); // https://stackoverflow.com/questions/21855842
    	        series4.setSmooth(false); // https://stackoverflow.com/questions/29014848
    	        series4.setMarkerStyle(MarkerStyle.DASH); // https://stackoverflow.com/questions/39636138
    	        chart.plot(leftdata);
    	        chart.plot(leftdata);  
            }
            chart.plot(leftdata);
            chart.plot(leftdata);
    	}
    	/**
    	 * 柱状图
    	 * @param sheet
    	 * @param desc
    	 * @param paramMap
    	 */
    	
    	private static void drawBarChart(XSSFSheet sheet,String desc,Map<String,Integer> paramMap) {
    		int col1 = paramMap.get("col1"),col2 = paramMap.get("col2"), row1=paramMap.get("row1"),row2 = paramMap.get("row2"),//绘图所在坐标,默认宽度为12列
    				numstartrow = paramMap.get("numstartrow"),numendrow = paramMap.get("numendrow"),numstartcol = paramMap.get("numstartcol"),numendcol = paramMap.get("numendcol"),
    				prostartrow = paramMap.get("prostartrow"),proendrow = paramMap.get("proendrow"),prostartcol = paramMap.get("prostartcol"),proendcol = paramMap.get("proendcol");
    			
    			int dx1=0;
    			int dy1=0;
    			int dx2=0;
    			int dy2=0;
    			XSSFDrawing drawing = sheet.createDrawingPatriarch();
    			XSSFClientAnchor anchor = drawing.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
    
    	        XSSFChart chart = drawing.createChart(anchor);
    	        XDDFChartLegend legend = chart.getOrAddLegend();
    	        legend.setPosition(LegendPosition.TOP_RIGHT);
    
    	        // Use a category axis for the bottom axis.
    	        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.TOP);//底部X轴
    	        bottomAxis.setTitle(desc+"交易情况汇总"); // https://stackoverflow.com/questions/32010765
    	        
    	        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);//左侧Y轴
    	        leftAxis.setTitle("交易量/金额");
    	        leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
    	        
    	        XDDFBarChartData data = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
    			CellRangeAddress cellRangeAddress=new CellRangeAddress(numstartrow, numendrow, numstartcol, numendcol);
    			XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet, cellRangeAddress);//横轴为第一列日期
    			
    			CellRangeAddress dataCellRangeAddress=new CellRangeAddress(numstartrow, numendrow, numstartcol, numendcol);
    			XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress);//纵轴为各个数据
    	        XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) data.addSeries(xs, ys1);
    	        series1.setTitle("交易量总计(笔)", null); // https://stackoverflow.com/questions/21855842
    	        
    	        CellRangeAddress dataCellRangeAddress2=new CellRangeAddress(prostartrow, proendrow,prostartcol,proendcol);
    	        XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress2);//纵轴为各个数据
    	        XDDFBarChartData.Series series2 = (XDDFBarChartData.Series) data.addSeries(xs, ys2);
    	        series2.setTitle("收入总计(元)", null); // https://stackoverflow.com/questions/21855842
    /*            
                for(int col=1;col<2;col++) {//数据列数:第一列为日期,其他列为对应数据。
        			CellRangeAddress dataCellRangeAddress=new CellRangeAddress(prostartrow, proendrow, prostartcol, proendcol);
    	            XDDFNumericalDataSource<Double> ys = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress);//纵轴为各个数据
    	            XDDFChartData.Series series1 = data.addSeries(xs, ys);
    	            series1.setTitle("111", null);
                }*/
                chart.plot(data);
                // in order to transform a bar chart into a column chart, you just need to change the bar direction
                XDDFBarChartData bar = (XDDFBarChartData) data;
                bar.setBarDirection(BarDirection.COL);
    	}
    	/**
    	 * 隐藏列
    	 * @param sheet
    	 * @param sheetName
    	 */
    	private static void hiddenColumn(XSSFSheet sheet, String sheetName) {
    		/*if(sheetName.equals("sheetname2")){//需要隐藏列
    			sheet.setColumnHidden(3, true);
    			sheet.setColumnHidden(4, true);
    			sheet.setColumnHidden(5, true);
    			sheet.setColumnHidden(6, true);
    			sheet.setColumnHidden(7, true);
    			sheet.setColumnHidden(8, true);
    		}*/
    	}
    
    	/**
    	 * 设置边框
    	 */
    	private static XSSFCellStyle setBorder(XSSFWorkbook wb){
    		XSSFCellStyle style = wb.createCellStyle();
    		style.setBorderBottom(BorderStyle.THIN);	//边框
    		style.setBorderTop(BorderStyle.THIN);		//边框
    		style.setBorderLeft(BorderStyle.THIN);		//边框
    		style.setBorderRight(BorderStyle.THIN);		//边框
    		return style;
    	}
    
    }
    

    4.测试类

    	import java.io.File;
    import java.util.Arrays;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    public class MyBarChartTest {
    	
    	public static void main(String args[]) {
    		String sheetName = "sheetname1";
    		try (XSSFWorkbook wb = new XSSFWorkbook()){
    			String  filePathDaily = "C:/Users/Administrator/Desktop/jfree/";
    			String fileName = "exceltest.xlsx";
    			
    			File filePath = new File(filePathDaily);
    			if(!filePath.exists()&&!filePath.isDirectory()){
    				filePath.mkdir();
    			}
    			//1.统计昨日交易量
    			File file = new File(filePathDaily+fileName);
    			
    			List<String> title=Arrays.asList("序号","类型","业务","合计:交易量","合计:收入",
    					"2019-01-01","2019-01-02","2019-01-03","2019-01-04","2019-01-05",
    					"2019-01-01收入","2019-01-02收入","2019-01-03收入","2019-01-04收入","2019-01-05收入");
    			List<String> titleStyle=Arrays.asList("int","String","String","int","double",
    					"int","int","int","int","int",
    					"double","double","double","double","double");
    			List<String> dates = Arrays.asList("2019-01-01","2019-01-02","2019-01-03","2019-01-04","2019-01-05");
    			
    			Map<String, List<Object>> day2ColValueList=new LinkedHashMap<String, List<Object>>();
    			day2ColValueList.put("类型1业务1",Arrays.asList("类型1","业务1",500,1000.00,100,50,100,150,100,200.00,150.00,200.00,250.00,200.00));
    			day2ColValueList.put("类型1业务2",Arrays.asList("类型1","业务2",400,800.00,80,60,80,100,80,160.00,130.00,160.00,190.00,160.00));
    			day2ColValueList.put("类型2业务1",Arrays.asList("类型2","业务1",600,1200.00,120,70,120,170,120,240.00,170.00,240.00,290.00,240.00));
    			day2ColValueList.put("类型2业务2",Arrays.asList("类型2","业务2",200,400.00,40,140,40,80,40,80.00,100.00,80.00,60.00,80.00));
    			MyExcleChart2.doWork(title, titleStyle, day2ColValueList,file,sheetName,wb,dates.size());
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    
    }
    
    

    5.结果展示
    在这里插入图片描述
    6.生成柱状折线图

      	<dependency>
    	  <groupId>org.apache.poi</groupId>
    	  <artifactId>poi</artifactId>
    	  <version>3.17</version>
    	</dependency>
    	<dependency>
    	  <groupId>org.apache.poi</groupId>
    	  <artifactId>poi-scratchpad</artifactId>
    	  <version>3.17</version>
    	</dependency>
    	<dependency>
    	  <groupId>org.apache.poi</groupId>
    	  <artifactId>poi-ooxml</artifactId>
    	  <version>3.17</version>
    	</dependency>
    	<dependency>
    	  <groupId>fr.opensagres.xdocreport</groupId>
    	  <artifactId>xdocreport</artifactId>
    	  <version>1.0.6</version>
    	</dependency>
    	<dependency>  
            <groupId> fr.opensagres.xdocreport</groupId>  
            <artifactId> org.apache.poi.xwpf.converter.core</artifactId>  
            <version> 1.0.6</version>  
    	</dependency>  
    	<dependency>
    		<groupId>fr.opensagres.xdocreport</groupId>
    		<artifactId>org.apache.poi.xwpf.converter.xhtml</artifactId>
    		<version>1.0.6</version>
    		</dependency>
    	<dependency>
    	  <groupId>org.apache.poi</groupId>
    	  <artifactId>poi-ooxml-schemas</artifactId>
    	  <version>3.17</version>
    	</dependency>
    	<dependency>
    	  <groupId>org.apache.poi</groupId>
    	  <artifactId>ooxml-schemas</artifactId>
    	  <version>1.3</version>
    	</dependency>
    
    package excel.test.com;
    
    import java.io.FileOutputStream;
    
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Chart;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFChart;
    import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
    import org.apache.poi.xssf.usermodel.XSSFDrawing;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
    import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
    import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
    import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
    import org.openxmlformats.schemas.drawingml.x2006.chart.STCrosses;
    import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
    import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
    import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
    
    public class BarAndLineChart2 {
    
        public static void main(String[] args) throws Exception {
        	XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet("Sheet1");
    
            Row row;
            Cell cell;
    
            row = sheet.createRow(0);
            row.createCell(0);
            row.createCell(1).setCellValue("Bars");
            row.createCell(2).setCellValue("Lines");
    
            for (int r = 1; r < 7; r++) {
                row = sheet.createRow(r);
                cell = row.createCell(0);
                cell.setCellValue("C" + r);
                cell = row.createCell(1);
                cell.setCellValue(new java.util.Random().nextDouble());
                cell = row.createCell(2);
                cell.setCellValue(new java.util.Random().nextDouble()*10d);
            }
    
            XSSFDrawing drawing = sheet.createDrawingPatriarch();
    //        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 0, 11, 15);
            XSSFClientAnchor anchor = (XSSFClientAnchor) drawing.createAnchor(0, 0, 0, 0, 4, 0, 11, 15);
    
            Chart chart = drawing.createChart(anchor);
    
            CTChart ctChart = ((XSSFChart)chart).getCTChart();  
            CTPlotArea ctPlotArea = ctChart.getPlotArea();
    
            //the bar chart
            CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
            CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
            ctBoolean.setVal(true);
            ctBarChart.addNewBarDir().setVal(STBarDir.COL);
    
            //the bar series
            CTBarSer ctBarSer = ctBarChart.addNewSer();
            CTSerTx ctSerTx = ctBarSer.addNewTx();
            CTStrRef ctStrRef = ctSerTx.addNewStrRef();
            ctStrRef.setF("Sheet1!$B$1");
            ctBarSer.addNewIdx().setVal(0);  
            CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
            ctStrRef = cttAxDataSource.addNewStrRef();
            ctStrRef.setF("Sheet1!$A$2:$A$7"); 
            CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
            CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
            ctNumRef.setF("Sheet1!$B$2:$B$7");
    
            //at least the border lines in Libreoffice Calc ;-)
            ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});   
    
            //telling the BarChart that it has axes and giving them Ids
            ctBarChart.addNewAxId().setVal(123456); //cat axis 1 (bars)
            ctBarChart.addNewAxId().setVal(123457); //val axis 1 (left)
    
            //the line chart
            CTLineChart ctLineChart = ctPlotArea.addNewLineChart();
            ctBoolean = ctLineChart.addNewVaryColors();
            ctBoolean.setVal(true);
    
            //the line series
            CTLineSer ctLineSer = ctLineChart.addNewSer();
            ctSerTx = ctLineSer.addNewTx();
            ctStrRef = ctSerTx.addNewStrRef();
            ctStrRef.setF("Sheet1!$C$1");
            ctLineSer.addNewIdx().setVal(1);  
            cttAxDataSource = ctLineSer.addNewCat();
            ctStrRef = cttAxDataSource.addNewStrRef();
            ctStrRef.setF("Sheet1!$A$2:$A$7"); 
            ctNumDataSource = ctLineSer.addNewVal();
            ctNumRef = ctNumDataSource.addNewNumRef();
            ctNumRef.setF("Sheet1!$C$2:$C$7");
    
            //at least the border lines in Libreoffice Calc ;-)
            ctLineSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});   
    
            //telling the LineChart that it has axes and giving them Ids
            ctLineChart.addNewAxId().setVal(123458); //cat axis 2 (lines)
            ctLineChart.addNewAxId().setVal(123459); //val axis 2 (right)
    
            //cat axis 1 (bars)
            CTCatAx ctCatAx = ctPlotArea.addNewCatAx(); 
            ctCatAx.addNewAxId().setVal(123456); //id of the cat axis
            CTScaling ctScaling = ctCatAx.addNewScaling();
            ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
            ctCatAx.addNewDelete().setVal(false);
            ctCatAx.addNewAxPos().setVal(STAxPos.B);
            ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis
            ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
    
            //val axis 1 (left)
            CTValAx ctValAx = ctPlotArea.addNewValAx(); 
            ctValAx.addNewAxId().setVal(123457); //id of the val axis
            ctScaling = ctValAx.addNewScaling();
            ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
            ctValAx.addNewDelete().setVal(false);
            ctValAx.addNewAxPos().setVal(STAxPos.L);
            ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis
            ctValAx.addNewCrosses().setVal(STCrosses.AUTO_ZERO); //this val axis crosses the cat axis at zero
            ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
    
            //cat axis 2 (lines)
            ctCatAx = ctPlotArea.addNewCatAx(); 
            ctCatAx.addNewAxId().setVal(123458); //id of the cat axis
            ctScaling = ctCatAx.addNewScaling();
            ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
            ctCatAx.addNewDelete().setVal(true); //this cat axis is deleted
            ctCatAx.addNewAxPos().setVal(STAxPos.B);
            ctCatAx.addNewCrossAx().setVal(123459); //id of the val axis
            ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
    
             //val axis 2 (right)
            ctValAx = ctPlotArea.addNewValAx(); 
            ctValAx.addNewAxId().setVal(123459); //id of the val axis
            ctScaling = ctValAx.addNewScaling();
            ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
            ctValAx.addNewDelete().setVal(false);
            ctValAx.addNewAxPos().setVal(STAxPos.R);
            ctValAx.addNewCrossAx().setVal(123458); //id of the cat axis
            ctValAx.addNewCrosses().setVal(STCrosses.MAX); //this val axis crosses the cat axis at max value
            ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
    
            //legend
            CTLegend ctLegend = ctChart.addNewLegend();
            ctLegend.addNewLegendPos().setVal(STLegendPos.B);
            ctLegend.addNewOverlay().setVal(false);
    
            FileOutputStream fileOut = new FileOutputStream("BarAndLineChart2.xlsx");
            wb.write(fileOut);
            fileOut.close();
        }
    }
    
    展开全文
  • 如何用excel表格上的数据做曲线。方法个连续区域,通过“框”输入地选中单元格,如选中A的A3:A8区域,在名称框输入“A3:A8”后回车,即可选中。方法二:选中满足条件的单元格,如选中A列中大于5的数据...

    如何用一个excel表格上的数据做曲线图。

    方法一个连续区域,通过“框”输入地选中单元格,如选中A列的A3:A8区域,在名称框中输入“A3:A8”后回车,即可选中。

    方法二:选中满足条件的单元格,如选中A列中大于5的数据单元格,通过查找的方式:

    ①选中A列;

    ②CTRL F打开查找窗口;

    ③查找内容输入“*”;

    ④“查找全部”;

    ⑤在查找框的下方,点击“值”,查找到的数据会按值的大小排序;

    ⑥选中第一个值大于5的数据,按下shift,选择最后一个数据;

    ⑦点击“关闭”,此时就选中了A列大于5的数据了。

    用EXCEL怎么做统计图,就图片中那样的统计图。。谢...

    Excel利用统据生成图表的方法有多这里介绍利用数据图生成图标的方法。

    软本:Office2013

    方法如下:

    1.利用下面的成绩数据统计并比较每个班的平均分:

    2.选择数据区域,插入数据透视图:

    3.设置数据透视图放置如下图所示:

    4.将班级拖动到轴字段中,得分拖动到值字段中:

    5.更改值字段的汇总方式为平均值:

    6.这样,就做好了一个基本的平均分比较的柱形图表:

    Excel如何制作统计数据

    Excel利用统据生成图表的方法有多这里介绍利用数据图生成图标的方法。

    软本:Office2013

    方法如下:

    1.利用下面的成绩数据统计并比较每个班的平均分:

    2.选择数据区域,插入数据透视图:

    3.设置数据透视图放置如下图所示:

    4.将班级拖动到轴字段中,得分拖动到值字段中:

    5.更改值字段的汇总方式为平均值:

    6.这样,就做好了一个基本的平均分比较的柱形图表:

    怎样把每天excel报表数据统计分析出来做成曲线图

    1、 的收集、录表格的设置,最终效果如图所示

    2、如图所示,选择行分析的图据范围

    3、如图所示,点击菜单栏目上的“插入”,选择“饼图”,再选择“三维饼图”,实际工作中,可以自己分析的内容选择相应的图形效果。

    4、 最终生成的效果,如下图1所示。接下来选择红色框标注位置的“图表布局”,找到如下图2所示的百分比。

    5、 最后一步,修改“图表标题”,把鼠标放到“图表标题”后,单击鼠标左键,录入和表格一致的标题名称,最终呈现的效果如下图所所示。

    怎样用Excel根据已有的数据自动生成这样的条形统计图

    这只需要两步吧~

    1、上传数据

    2、拖拽做图

    以上工具:BDP个人版

    展开全文
  • python操作excel表格有不少组件,如:xlwt、openpyxl、pandas、xlrd、xlwings,一些组件在使用时有问题;这里选用xlwings,从原Excel报表统计后再生成的新Excel表格。

    0、前言

             因产品的同学试着用python统计Excel表格,但百度的程序又一直有各种问题, 所以叫我帮用python实现一个统计Excel表格程序;我之前也没有写过python操作Excel表格的程序,快速搜索实现了一下,还是发现一些问题,所以分享一下。

    1、需求       

            我们使用TAPD管理项目,这里从TAPD导出的原Excel报表,统计后再生成的新Excel表。
            原Excel报表是任务(标题)的信息列表,需要按需求分类分别统计各处理人的预估工时,如下(图1原始Excel表格有200多行、图2统计生成的新Excel表格):

    原始Excel表格
    统计生成的新Excel表格

    2、python操作excel表格说明

            python操作excel表格有不少组件,如:xlwt、openpyxl、pandas、xlrd、xlwings
            
            一些组件在使用时有问题,如下:
                    https://blog.csdn.net/weixin_42555985/article/details/102872781
                    https://www.jianshu.com/p/8640abf11297
            
            这里选用xlwings,xlwings使用接近VBA的语法从Python自动与Excel交互,注意是使用到Excel软件;
            官方文档及一些参考如下:
                    https://docs.xlwings.org/en/stable/index.html
                    https://blog.csdn.net/whalefall/article/details/102665002
                    http://www.dszhp.com/xlwings-range.html
                    https://www.cnblogs.com/cyanrose/p/12059040.html
                    https://blog.csdn.net/lh_hebine/article/details/104559382
            
            当然xlwing使用中也发现一个问题,range().expand()选择表格范围时无法识别一行/一列中的空值,遇到空值默认读取终止:
                    https://blog.csdn.net/weixin_44781801/article/details/88692982
            下面程序会跳过一行/一列的范围选择问题。

    3、python xlwings操作excel表格程序

            目录下有111.py脚本、原始excel表格222.xls和生成的excel表格333.xls;

            直接运行程序: $ python 111.py
            即可实现222.xls --统计、生成--> 333.xls,效果如前面的excel表格图片;
            下面是python 111.py脚本程序,可在python2.7运行:

    # -*- coding: utf-8 -*-
    import xlwings as xw
    import sys
    
    
    class Story(object):
    
        def __init__(self, handler, workingHours, demandClassification):
            self.handler = handler
            self.workingHours = workingHours
            self.demandClassification = demandClassification
    
        def get_handler(self):
            return self.handler
    
        def get_workingHours(self):
            return self.workingHours
    
        def get_demandClassification(self):
            return self.demandClassification
    
        def __str__(self):
            return "处理人:" + self.handler + ", 预估工时:" + str(self.workingHours) + ", 需求分类:" + self.demandClassification
    
    
    filePath = r'222.xls'
    newFilePath = r'333.xls'
    
    
    reload(sys)
    sys.setdefaultencoding('utf8')
    
    app = None
    wb = None
    wbNew = None
    appNew = None
    try:
        app = xw.App(visible=False, add_book=False)
        app.display_alerts = False
        app.screen_updating = False
        wb = app.books.open(filePath)
    
        sht = wb.sheets.active
        # shtName=sht.name
        # print(shtName)
    
        rowCount = sht.range('A1').expand('table').rows.count
        # print(rowCount)
        data = sht.range('A3:G' + str(rowCount)).value
        # print(data)
        storyList = list()
        for i in range(len(data)):
            # print(data[i])
            # for j in range(len(data[i])):
                # print(data[i][j])
            handler = data[i][1]
            workingHours = data[i][4]
            demandClassification = data[i][5]
            if handler is None:
                handler = "未安排处理人"
            if workingHours is None:
                workingHours = 0
            if demandClassification is None:
                demandClassification = "未知需求分类"
    
            story = Story(str(handler), int(str(workingHours)),
                          str(demandClassification))
            storyList.append(story)
            # print(str(story))
    
        if len(storyList) > 0:
            handlerSet = set()
            demandClassificationSet = set()
            workingHoursMap = dict()
            for story in storyList:
                handlerSet.add(story.get_handler())
                demandClassificationSet.add(story.get_demandClassification())
                key = str(story.get_handler() + story.get_demandClassification())
                if workingHoursMap.get(key) is None:
                    workingHoursMap[key] = 0
                workingHoursMap[key] = workingHoursMap.get(
                    key) + story.get_workingHours()
    
            handlerList = list(handlerSet)
            demandClassificationList = list(demandClassificationSet)
            workingHoursList = []
            handlerGbkList = []
            demandClassificationGbkList = []
            change = False
            for j in range(len(demandClassificationList)):
                workingHoursList.append([])
                for k in range(len(handlerList)):
                    key = str(handlerList[k] + demandClassificationList[j])
                    workingHoursList[j].append(workingHoursMap.get(key))
                    if change is False:
                        handlerGbkList.append(handlerList[k].encode("gbk"))
                change = True
                demandClassificationGbkList.append(
                    demandClassificationList[j].encode("gbk"))
    
            try:
                appNew = xw.App(visible=False, add_book=False)
                appNew.display_alerts = False
                appNew.screen_updating = False
                wbNew = appNew.books.add()
                shtNew = wbNew.sheets['sheet1']
    
                shtNew.range('A2').options(
                    transpose=True).value = demandClassificationGbkList
                shtNew.range('B1').value = handlerGbkList
                shtNew.range('B2').value = workingHoursList
    
                wbNew.save(newFilePath)
    
            finally:
                if wbNew is not None:
                    wbNew.close()
                if appNew is not None:
                    appNew.quit()
    finally:
        if wb is not None:
            wb.close()
        if app is not None:
            app.quit()
    

     

    展开全文
  • # y的值包含'6','8'的所有行。 # df_filter = df[df['city'].isin(['beijing'])] # df_agg.to_excel(r'data\厂家.xlsx', sheet_name='2') # index_list = df.index.unique().levels[0] print(df_agg.ix['...
  • 需要统计列中每个院系的总数并画成柱状 有三个这样的表格,需要统计则这三个表格所有的每个院系的数量 二.步骤: 1.读xls文件 xls_file17 = xl.open_workbook("文件路径.xls") 2.获取该数据 xls_sheet1 = []...
  • 在实际工作,用Excel生成统计图模板文件,再通过Java程序将数据输入Excel文件形成需要的统计图,但折线图或条形图可能由于没有确定的统计数据值,只能预设最大的统计量,如下图 最多预设显示19个数据值,实际...
  • /// 导出GridView控件的数据,并在Excel文件中生成图表 /// public class ExportExcelAction { /// /// 私有变量,待绘制图表对象的集合 /// private List<DataChartObject> _DataChartObjectList; ...
  • 使用Excel VBA制度成绩统计图
  • Python读取excel生成正态分布

    千次阅读 2020-04-04 15:04:46
    在制造业工厂为了统计产品尺寸的分布程度,需要制作正态分布excel 没有专门的概率分布制作比较麻烦。使用python容易实现。 运行环境: win10 win8 win7 64位操作系统 python 3.6 以上 第三方库: numpy ...
  • 需以此表格为依据导入到数据库生成一个学校;或者更新个学校的信息; 难点: Excel表格 学校的logo 是图片;是jpg,png,等格式;png格式是 如何正确获取到这张图片。 使用的第三方处理excel表格的库为: \...
  • 统计Excel内容值的重复个数的绿色在线工具,支持统计重复行的个数,统计重复单元格的个数,统计单元格内文字的重复数,个人用js做的 (注意要使用谷歌浏览器) ...、如果是统计或某几列相同的行重复数统计使...
  • 【Python3】xlwt/xlrd模块读取和新建excel生成直方

    千次阅读 多人点赞 2020-11-26 16:20:52
    在sheet定位关键字插入链接与图片如何插入段漂亮的代码片生成一个适合你的列表创建个表格设定内容居中、居左、居右SmartyPants创建个自定义列表如何创建个注脚注释也是必不可少的KaTeX数学公式新的甘特...
  • 更方便其他小伙伴们帮助我们导入数据,小伙伴们都知道,在excel中设置下拉菜单很简单,直接用数据有效性-序列就可以实现,今天小编跟小伙伴分享一个,可以显示多列内容,选取后只输入其中一列的内容,效果如下所示:...
  • Excel表格数据生成ECharts图表

    千次阅读 多人点赞 2019-03-25 23:48:34
    然后就做了这么个小东西,通过html页面,选择个有数据(固定格式)的Excel文件,根据Excel里面的数据,生成对应的ECharts图表,如折线、柱状。 效果如下: 其实大家仔细看就知道Excel的数据和转换后的...
  • 前段时间,因为客户需要,要做个导出excel文件功能,并能生成原生的图表的(不是把图片插入到excel文档),找了很多文档看,也看了很多别人的代码,个人也总结了一下,不足之处,请各位大牛谅解. 需要的jar包如下(直接使用...
  • Python实现Excel生成汇总直方

    千次阅读 2020-01-08 12:10:35
    # -*- coding: utf-8 -*- ...import xlwt # 写入excel文件的库 import xlrd #读取Excel import time import datetime import geohash #把经纬度转换为geohash import random start_time=datetime.datet...
  • 直接用Excel打开时,所有数据都会挤在一列里。 我们可以新建一个Excel,然后点击“数据”,再点击“自文本” 当数据生成标准的Excel之后,再点击“插入”,选择“图表”的各项,生成相应的图形。 转载于:...
  • python:从excel中提取高频词生成词云

    万次阅读 多人点赞 2020-01-14 15:05:08
    分块功能说明3.1统计词频3.2过滤3.3生成词云4.结果5.工程代码 1.需要的库 pip install pandas pkuseg numpy matplotlib PIL wordcloud pkuseg是个分词器:https://github.com/lancopku/pkuseg-python pandas,...
  • 使用easypoi导出excel实现动态

    万次阅读 热门讨论 2019-03-01 10:41:20
    一列进行纵向动态合并 自己的一个使用,记录一下 实现效果 变更前样式 变更后样式 代码解析 动态生成列头 private List&lt;ExcelExportEntity&gt; dynamicNewAddExcel(List&lt;Platfo...
  • 最近在做SpringBoot项目,项目需要上传Excel文件,对Excel文件的内容进行解析,然后将需要的数据存入数据库,同时还要根据用户的需求,将需要的内容生成Excel文件,并下载下来。本篇主要是介绍Excel文件的生成...
  • 以下是中国2018年8月~2019年7月的各省CPI数据,由于我要利用该SPSS软件进行统计,需要将其化为三很多行的数据(行数=省市数*时间数,行表示个对应关系),由此引出了我对excel中OFFSET函...
  • SUMPRODUCT(1/COUNTIF(A3:A302,A3:A302))是什么意思? 统计,之间有多少个重复的项目. COUNTIF(A3:A302,A3:A302)的意思就是统计A3在A3:A302有多少个,统计A4在A3:A302有多少个……统计统计A302在A3:... 比如说这么...
  • python的Pandas模块读取Excel非常方便,可以生成各种,这不是重点。重点是如何在生成的柱状上带上值的标识,这个需要费些心思。 这里举个百分比的例子,比如我有个表,统计每一年这五个省GDP占全国的比例。 ...
  • 有点困难在于数据是从哪一行哪一列取,还有标题是不是要默认显示,这些都属于微调 分解步骤三、绘制饼图 参考网址:https://www.osgeo.cn/openpyxl/charts/pie.html pie = PieChart ( ) labels = ...
  • Python统计文件某一列相同数据出现的个数并插入柱状 说明 (模块:xlsxwriter) Python访问文件一列相同数据出现的个数,新建xlsx文件并根据数据插入相应柱状 图示:(以上次python爬虫获取的文件为例) ...
  • Excel生成频率分布表及频率分布直方 福建省南平高级中学 郑定华 353000 手机 13859389247 ...关键词:Excel 统计 直方 生成统计教与学数据进行统计分析、绘制统计图表等,要涉及许多
  • excel中如何计算个数,使用这个方法,给出汉字描述,自动生成Excel统计公式。支持Excel中分类统计数量 地址:http://www.excelfb.com/ 点击:Excel自动写公式工具(根据汉字描述写公式,支持合并单元格公式) ...
  • HSSF生成EXCEL表格学习分享

    千次阅读 2020-03-14 14:57:11
      最近这周刚安排了个需求是生成一EXCEL表格,目前没有做过生成EXCEL相关的代码开发,就参考项目下载EXCEL功能的代码,以及百度搜素。最后花了我天时间终于琢磨透这块内容,然后,觉得这块内容挺有意思,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 12,222
精华内容 4,888
关键字:

对excel中的一列生成统计图