poi读写Excel表格Demo

一、导入poi需要的jar包，最新jar包下载地址http://poi.apache.org/download.html导入导出Excel表所需的jar包如图所示。

 二、导入导出Excel 例子：

 

 

package com.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
	
	public static final String HEADERINFO="headInfo";
	public static final String DATAINFON="dataInfo";
	
	/**
	 * 
	 * @Title: getWeebWork
	 * @Description: TODO(根据传入的文件名获取工作簿对象(Workbook))
	 * @param filename
	 * @return
	 * @throws IOException 
	 */
	public static Workbook getWeebWork(String filename) throws IOException{
		Workbook workbook=null;
		if(null!=filename){
			String fileType=filename.substring(filename.lastIndexOf("."),filename.length());
			FileInputStream fileStream = new FileInputStream(new File(filename));
			if(".xls".equals(fileType.trim().toLowerCase())){
				workbook = new HSSFWorkbook(fileStream);// 创建 Excel 2003 工作簿对象
			}else if(".xlsx".equals(fileType.trim().toLowerCase())){
				workbook = new XSSFWorkbook(fileStream);//创建 Excel 2007 工作簿对象
			}
		}
		return workbook;
	}
	/**
	 * 
	 * @Title: writeExcel
	 * @Description: TODO(导出Excel表)
	 * @param pathname:导出Excel表的文件路径
	 * @param map：封装需要导出的数据(HEADERINFO封装表头信息，DATAINFON：封装要导出的数据信息,此处需要使用TreeMap)
	 * 例如： map.put(ExcelUtil.HEADERINFO,List<String> headList);
	 * 		 map.put(ExcelUtil.DATAINFON,List<TreeMap<String,Object>>  dataList);
	 * @param wb
	 * @throws IOException
	 */
	public static void writeExcel(String pathname,Map<String,Object> map,Workbook wb) throws IOException{
		if(null!=map && null!=pathname){
			List<Object> headList = (List<Object>) map.get(ExcelUtil.HEADERINFO);
			List<TreeMap<String,Object>> dataList =  (List<TreeMap<String, Object>>) map.get(ExcelUtil.DATAINFON);
			CellStyle style = getCellStyle(wb);
			Sheet sheet = wb.createSheet();
			/**
			 * 设置Excel表的第一行即表头
			 */
			Row row =sheet.createRow(0);
			for(int i=0;i<headList.size();i++){
				Cell headCell = row.createCell(i);
				headCell.setCellType(Cell.CELL_TYPE_STRING);
				headCell.setCellStyle(style);//设置表头样式
				headCell.setCellValue(String.valueOf(headList.get(i)));
			}
			
			for (int i = 0; i < dataList.size(); i++) {
				Row rowdata = sheet.createRow(i+1);//创建数据行
				TreeMap<String, Object> mapdata =dataList.get(i);
				Iterator it = mapdata.keySet().iterator();
				int j=0;
				while(it.hasNext()){
					String strdata = String.valueOf(mapdata.get(it.next()));
					Cell celldata = rowdata.createCell(j);
					celldata.setCellType(Cell.CELL_TYPE_STRING);
					celldata.setCellValue(strdata);
					j++;
				}
			}
			File file = new File(pathname);
			OutputStream os = new FileOutputStream(file);
			os.flush();
			wb.write(os);
			os.close();
		}
	}
	/**
	 * 
	 * @Title: getCellStyle
	 * @Description: TODO（设置表头样式）
	 * @param wb
	 * @return
	 */
	public static CellStyle getCellStyle(Workbook wb){
		CellStyle style = wb.createCellStyle();
		Font font = wb.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short)12);//设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
		style.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setAlignment(HSSFCellStyle.SOLID_FOREGROUND);//让单元格居中
		//style.setWrapText(true);//设置自动换行
		style.setFont(font);
		return style;
	}
	
	/**
	 * 
	 * @Title: readerExcelDemo
	 * @Description: TODO(读取Excel表中的数据)
	 * @throws IOException
	 */
	public static void readerExcelDemo() throws IOException{
		/**
		 * 读取Excel表中的所有数据
		 */
		Workbook workbook=getWeebWork("E:/结算单503411妥投结算数据.xlsx");
		System.out.println("总表页数为："+workbook.getNumberOfSheets());//获取表页数
		Sheet sheet =workbook.getSheetAt(0);
		int rownum=sheet.getLastRowNum();//获取总行数
		for (int i = 0; i < rownum; i++) {
			Row row =sheet.getRow(i);
			Cell orderno = row.getCell(2);//获取指定单元格中的数据
			System.out.println(orderno.getCellType());
			short cellnum=row.getLastCellNum(); //获取单元格的总列数
			for(int j=row.getFirstCellNum();j< row.getLastCellNum();j++){
				Cell celldata = row.getCell(j);
				System.out.print(celldata+"\t");
			}
			System.out.println();
		}
		
		/**
		 * 读取指定位置的单元格
		 */
		Row row1 = sheet.getRow(1);
		Cell cell1 = row1.getCell(2);
		System.out.print("(1,2)位置单元格的值为："+cell1);
		BigDecimal big = new BigDecimal(cell1.getNumericCellValue());//将科学计数法表示的数据转化为String类型
		System.out.print("\t"+String.valueOf(big));
		
	}
	
	public static void main(String[] args) throws IOException {
		readerExcelDemo();
		
		Workbook wb = new XSSFWorkbook();
		Map<String,Object> map = new HashMap<String,Object>();
		List headList = new ArrayList();//表头数据
		headList.add("下单时间");
		headList.add("结账时间");
		headList.add("订单编号");
		headList.add("订单金额");
		headList.add("用户名");
		
		List dataList = new ArrayList();//表格内的数据
		for (int i = 0; i < 15; i++) {
			TreeMap<String,Object> treeMap = new TreeMap<String, Object>();//此处的数据必须为有序数据，所以使用TreeMap进行封装
			treeMap.put("m1", "2013-10-"+i+1);
			treeMap.put("m2", "2013-11-"+i+1);
			treeMap.put("m3", "20124"+i+1);
			treeMap.put("m4", 23.5+i+1);
			treeMap.put("m5", "张三_"+i);
			dataList.add(treeMap);
		}
		TreeMap<String,Object> treeMap1 = new TreeMap<String, Object>();
		treeMap1.put("asd", null);
		treeMap1.put("猪头", "zhutou");
		dataList.add(treeMap1);
		map.put(ExcelUtil.HEADERINFO, headList);
		map.put(ExcelUtil.DATAINFON, dataList);
		writeExcel("E:/test.xlsx", map, wb);
	}
}

 导出结果：