poi-Excel导出数据实例

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/zhangminemail/article/details/75635423

1)包结构

下载链接:http://download.csdn.net/detail/evangel_z/4107089


  


2)Main类

package com.jj.zemel;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jfinal.kit.PropKit;
import com.jfinal.kit.StrKit;
import com.jj.zemel.service.StarRatioService;
import com.jj.zemel.utils.ExcelUtil;

public class Main {

	
	private static final String[] keys = {"财会"};
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		StarRatioService srs = new StarRatioService();
		Map<String,List<String[]>> dataMap = new HashMap<>();
		List<String> sheetNames = new ArrayList<>();
		
		for (int i = 0; i < keys.length; i++) {
			String key = PropKit.get(keys[i]);
			if(StrKit.isBlank(key)){
				continue;
			}
			
			List<String[]> list = srs.querySheetData(key);
			dataMap.put(keys[i], list);
			sheetNames.add(keys[i]);
		}
		srs.closeConnection();
		
		ExcelUtil.writeXlsx("0719星级占比.xlsx", dataMap, sheetNames.toArray(new String[]{}));
		
	}
	

}


3)Service类

package com.jj.zemel.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.jj.zemel.utils.CommonUtil;

public class StarRatioService {
	
	private static final String[] head1 = {"", "", "", "星级占比"};
	private static final String[] head2 = {"二级类目", "科目", "章/节", "1星占比", "2星占比", "3星占比", "4星占比", "5星占比"};

	private Connection connection;
	
	public StarRatioService(){
		this("config.properties");
	}
	
	public StarRatioService(String excelPath){
		CommonUtil.initProperties(excelPath);
		connection = CommonUtil.getConnection();
	}
	
	
	public List<String[]> querySheetData(String ids){
		
		List<String[]> list = new ArrayList<>();
		list.add(head1);
		list.add(head2);
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(" (SELECT b2.name FROM b_category b2 WHERE b2.id = bc.parent_id) as '二级类目' , ");
		sql.append(" bc.name AS '科目', ");
		sql.append(" p.title AS '章节名', ");
		sql.append(" IFNULL((select CONCAT(ROUND((count(t.rate)/(select count(t2.rate) from b_testitems t2 where t2.chapter_id=p.id)*100),0),'%') as counts from b_testitems t where t.chapter_id=p.id and t.rate=1),'0%')  as '一星', ");
		sql.append(" IFNULL((select CONCAT(ROUND((count(t.rate)/(select count(t2.rate) from b_testitems t2 where t2.chapter_id=p.id)*100),0),'%') as counts from b_testitems t where t.chapter_id=p.id and t.rate=2) ,'0%') as '二星', ");
		sql.append(" IFNULL((select CONCAT(ROUND((count(t.rate)/(select count(t2.rate) from b_testitems t2 where t2.chapter_id=p.id)*100),0),'%') as counts from b_testitems t where t.chapter_id=p.id and t.rate=3) ,'0%') as '三星', ");
		sql.append(" IFNULL((select CONCAT(ROUND((count(t.rate)/(select count(t2.rate) from b_testitems t2 where t2.chapter_id=p.id)*100),0),'%') as counts from b_testitems t where t.chapter_id=p.id and t.rate=4) ,'0%') as '四星', ");
		sql.append(" IFNULL((select CONCAT(ROUND((count(t.rate)/(select count(t2.rate) from b_testitems t2 where t2.chapter_id=p.id)*100),0),'%') as counts from b_testitems t where t.chapter_id=p.id and t.rate=5) ,'0%') as '五星' ");
		sql.append(" from b_chapter p LEFT JOIN b_category bc ON p.category_id = bc.id where p.category_id IN ("+ids+") ");
		sql.append(" ORDER BY bc.name  ");
		
//		PreparedStatement ps = null;
		Statement statement = null;
		ResultSet rs = null;
		ResultSetMetaData rsm = null;//获取列信息
		
		try {
//			ps = connection.prepareStatement(sql.toString());
//			ps.setString(1, PropKit.get(idsKey));
//			rs = ps.executeQuery();
			
			statement = connection.createStatement();
			rs = statement.executeQuery(sql.toString());
			
			rsm =rs.getMetaData();
			int columns = rsm.getColumnCount();
			System.out.println("columns:"+columns);
			int rows = 0;
			while(rs.next())
		    {
				rows = rows + 1;
				List<String> strList = new ArrayList<>();
			    for(int i=1;i<=columns;i++){
			    	strList.add(rs.getString(i));
//			    	System.out.print(rs.getString(i));
//			    	System.out.print("\t\t");
			    	
			    }
			    list.add(strList.toArray(new String[]{}));
//			    System.out.println();
		    }
			System.out.println("rows:"+rows);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				System.out.println("开始关闭statement...");
				rs.close();
//				ps.close();
				statement.close();
				System.out.println("关闭statement成功.");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return list;
	}
	
	/**
	 * 关闭连接
	 */
	public void closeConnection(){
		
		if(connection != null){
			System.out.println("开始关闭connection连接...");
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			System.out.println("关闭connection连接成功.");
		}
	}
}


4)工具类

package com.jj.zemel.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import com.jfinal.kit.PropKit;
import com.jfinal.kit.StrKit;

public class CommonUtil {

	public static String filePath = "";
	
	/**
     * 初始化
     */
    public static void initProperties(String proFile){
    	
    	PropKit.use(proFile);
    	
    	checkVar();
    	 
    }
    
    /**
	 * 获取数据库连接
	 * @return
	 */
    public static Connection getConnection() {  
    	
    	Connection connection = null;
        // 加载驱动,建立连接  
        try {  
        	System.out.println("开启db连接...");
            Class.forName(PropKit.get("driver_name", "com.mysql.jdbc.Driver"));  
            connection = DriverManager.getConnection(PropKit.get("db_url"), PropKit.get("username"), PropKit.get("password"));  
            System.out.println("db连接成功.");  
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }   
        
        return connection;
    }  
    
	private static void checkVar(){
		filePath = PropKit.get("excel_path", "新项目考点星级规则.xlsx");
		if(StrKit.isBlank(PropKit.get("db_url"))){
    		System.out.println("db_url属性值为空。");
    	}
    	if(StrKit.isBlank(PropKit.get("username"))){
    		System.out.println("username属性值为空。");
    	}
    	if(StrKit.isBlank(PropKit.get("password"))){
    		System.out.println("password属性值为空。");
    	}
	}
    
}


package com.jj.zemel.utils;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
	
	//写入Xlsx
    public static void writeXlsx(String fileName, Map<String, List<String[]>> map, String[] sheetNames) {
    	System.out.println("写入excel开始...");
    	try {
            XSSFWorkbook wb = new XSSFWorkbook();
            for(int sheetnum=0; sheetnum < map.size(); sheetnum++){
                XSSFSheet sheet = wb.createSheet(sheetNames[sheetnum]);
                
                List<String[]> list = map.get(sheetNames[sheetnum]);
                for(int i=0;i<list.size();i++){
                    XSSFRow row = sheet.createRow(i);
                    String[] str = list.get(i);
                    for(int j=0;j<str.length;j++){
                        XSSFCell cell = row.createCell(j);
                        cell.setCellValue(str[j]);
                    }
                }
//                addHeader(sheet);
                
            }
            FileOutputStream outputStream = new FileOutputStream(fileName);
            wb.write(outputStream);
            outputStream.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        
        System.out.println("写入excel成功.");
    }
    
    public static void addHeader(XSSFSheet sheet){
    	
    	// 设置合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
    	
    }
}



5、App读取类

package com.jj.zemel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.Cell;
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 com.jfinal.kit.PropKit;
import com.jfinal.kit.StrKit;
import com.mysql.jdbc.StringUtils;

public class App {
	
	private static String filePath = "";
	private static String dbUrl = "";
	private static String username = "";
	private static String password = "";
	private Connection connection = null;
	private Statement statement = null;

	/**
	 * @param args
	 */
	public static void main(String[] args) {


		App app = new App();
		app.init();
		try {
			app.readData();
		} catch (Exception e) {
			app.close();
			e.printStackTrace();
		}finally{
			app.close();
		}
		
        
//        // 获取JDBC连接  
//        Statement statement = null;  
//        // 解析数据  
//       
//        Workbook hwb = null;  
//        
//        try {  
//            File file = new File(filePath);  
//            hwb = new XSSFWorkbook(new FileInputStream(file));  
//              
////            System.err.println("hwb.getNumberOfSheets():"+hwb.getNumberOfSheets());
//            
//            
//            
//            
//            HSSFSheet e = hwb.getSheet("sheet名");  
//            statement = connection.createStatement();  
//  
//            for (int i = 1; i <= e.getLastRowNum(); ++i) {  
//                HSSFRow row = e.getRow(i);  
//                Map<Integer, String> map = new HashMap<Integer, String>();  
//  
//                for (int j = 0; j < row.getLastCellNum(); ++j) {  
//                    HSSFCell cell = row.getCell(j);  
//                    cell.setCellType(1);  
//                    map.put(Integer.valueOf(j), cell.getStringCellValue());  
//                }  
//                  
//                // 查询主机是否存在  
//                if(StringUtils.isNotBlank(map.get(0))){  
//                    continue;  
//                }  
//                ResultSet resultSet = statement.executeQuery("select * from `hosts` where `name`='" + map.get(1) + "'");  
//                if(!resultSet.next()){  
//                    continue;  
//                }  
//  
//                // 插入数据库连接  
//                StringBuffer sqlStr = new StringBuffer();  
//                sqlStr.append("INSERT INTO table values(...)");  
//                statement.addBatch(sqlStr.toString());  
//            }  
//              
//              
//            e = hwb.getSheet("sheet名");  
//            for (int i = 1; i <= e.getLastRowNum(); ++i) {  
//                HSSFRow row = e.getRow(i);  
//                Map<Integer, String> map = new HashMap<Integer, String>();  
//  
//                for (int j = 0; j < row.getLastCellNum(); ++j) {  
//                    HSSFCell cell = row.getCell(j);  
//                    String value = "";  
//                    if(cell != null){  
//                        cell.setCellType(1);  
//                        value = cell.getStringCellValue();  
//                    }  
//                    map.put(Integer.valueOf(j), value);  
//                }  
//                  
//                // 查询主机是否存在  
//                if(StringUtils.isNotBlank(map.get(0))){  
//                    continue;  
//                }  
//                ResultSet resultSet = statement.executeQuery("select * from `hosts` where `name`='" + map.get(1) + "'");  
//                if(!resultSet.next()){  
//                    continue;  
//                }  
//  
//                // 插入数据库连接  
//                StringBuffer strBuffer = new StringBuffer();  
//                strBuffer.append("INSERT INTO table values(...)");  
//                statement.addBatch(strBuffer.toString());  
//            }  
//              
//            statement.executeBatch();  
//            System.out.println("数据插入成功.");  
  
//        } catch (FileNotFoundException e) {  
//            e.printStackTrace();  
//        } catch (IOException e) {  
//            e.printStackTrace();  
//        } catch (SQLException e) {  
//            e.printStackTrace();  
//        }finally{  
//            try {  
//                System.out.println("关闭连接.");  
//                statement.close();  
//            } catch (SQLException e) {  
//                e.printStackTrace();  
//            }  
//        }  
          
    }  
      
	/**
	 * 获取数据库连接
	 * @return
	 */
    private void initConnection() {  
        
        // 加载驱动,建立连接  
        try {  
        	System.out.println("开启db连接...");
            Class.forName(PropKit.get("driver_name", "com.mysql.jdbc.Driver"));  
            connection = DriverManager.getConnection(dbUrl, username, password);  
            System.out.println("db连接成功.");  
            
            statement = connection.createStatement();
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }   
        
    }  
    
    /**
     * 初始化
     * @throws Exception
     */
    private void init(){
    	
    	 PropKit.use("config.properties");
    	 filePath = PropKit.get("excel_path", "新项目考点星级规则.xlsx");
    	 dbUrl = PropKit.get("db_url");
    	 username = PropKit.get("username");
    	 password = PropKit.get("password");
    	 
    	 if(StrKit.isBlank(dbUrl)){
    		 System.out.println("db_url属性值为空。");
    	 }
    	 if(StrKit.isBlank(username)){
    		 System.out.println("username属性值为空。");
    	 }
    	 if(StrKit.isBlank(password)){
    		 System.out.println("password属性值为空。");
    	 }
    	 
    	 // 数据库连接初始化
    	 initConnection();
    	 
    }
    
    private void close(){
    	
    	System.out.println("开始关闭连接...");
		try {
			statement.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("关闭连接成功...");
    }
    
    private Workbook getWorkbook(){
    	Workbook hwb = null;
    	
    	File file = new File(filePath);  
        try {
			hwb = new XSSFWorkbook(new FileInputStream(file));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			try {
				hwb = new HSSFWorkbook(new FileInputStream(file));
			} catch (FileNotFoundException e1) {
				e1.printStackTrace();
			} catch (IOException e1) {
				e1.printStackTrace();
			} 
		} 
    	
        return hwb;
    }
    
    private void readData()throws Exception{
    	
    	Workbook hwb = getWorkbook();
    	int sheetNum = hwb.getNumberOfSheets();
    	
    	for (int i = 0; i < sheetNum; i++) {
    		excuteSheet(hwb.getSheetAt(i));
		}
    }
    
    private void excuteSheet(Sheet e)throws Exception{
    	
    	
    	for (int i = 2; i <= e.getLastRowNum(); ++i) {  
    		System.out.println("第 "+(i-1)+" 行");
            Row row = e.getRow(i);  
            Map<Integer, String> map = new HashMap<Integer, String>();  
            
            for (int j = 0; j < row.getLastCellNum(); ++j) {  
                Cell cell = row.getCell(j);  
                cell.setCellType(1);  
                map.put(Integer.valueOf(j), cell.getStringCellValue());  
            }  
            
//            System.out.println(map);
              
//            ResultSet resultSet = statement.executeQuery("select * from `hosts` where `name`='" + map.get(1) + "'");  
//            if(!resultSet.next()){  
//                continue;  
//            }  

            // 插入数据库连接  
//            StringBuffer sqlStr = new StringBuffer();  
//            sqlStr.append("INSERT INTO table values(...)");  
//            statement.addBatch(sqlStr.toString());  
        }  
    	
    }
		
}




展开阅读全文

没有更多推荐了,返回首页