Java操作Excel

admin 发布于 2020-03-18T11:36:57 评论(0) 阅读(50)

分类:Java

标签:

利用poi创建Excel文件。

/**
	 * 创建Excel文件方法,供外部调用
	 * @param fileName 文件名
	 * @param list<HashMap<String,String>>
	 * @return true 创建成功  false 创建失败
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static boolean createExcel(String fileName,List <HashMap<String,String>> list) throws FileNotFoundException, IOException{
		if(fileName.endsWith(".xls")){
			return createExcel2003(fileName, list);
		}else if (fileName.endsWith(".xlsx")){
			return createExcel2007(fileName, list);
		}else {
			return false;
		}
	}
	/**
	 * 
	 * @param fileName
	 * @param list
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static boolean createExcel2003(String fileName,List <HashMap<String,String>> list) throws FileNotFoundException, IOException{
		if(list==null||list.size()==0){
			return false;
		}
		HSSFWorkbook xls = new HSSFWorkbook();
		HSSFSheet sheet= xls.createSheet();
		HashMap<String,String> map=null;
		//遍历list
		for(int i=0;i<list.size();i++){
			HSSFRow rows= sheet.createRow(i);
			map=list.get(i);
			Set<String> set=map.keySet();
			Iterator<String> iterator=set.iterator();
			int j=0;
			while(iterator.hasNext()){
				String key=iterator.next();
				HSSFCell cell=rows.createCell(j);
				cell.setCellValue(map.get(key));//赋值
				j++;
			}
		}
		FileOutputStream out=new FileOutputStream(new File(fileName));
		xls.write(out);
		xls.close();
		out.flush();
		out.close();
		return true;
	}
	/**
	 * 
	 * @param fileName
	 * @param list
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static boolean createExcel2007(String fileName,List <HashMap<String,String>> list) throws FileNotFoundException, IOException{
		if(list==null||list.size()==0){
			return false;
		}
		//创建对象
		XSSFWorkbook xlsx = new XSSFWorkbook();
		//创建sheet
		XSSFSheet sheet= xlsx.createSheet("工作簿");
		//创建map对象,接收传进的参数
		HashMap<String,String> map=null;
		//遍历list
		for(int i=0;i<list.size();i++){
			//创建row对象,代表每一行
			XSSFRow rows= sheet.createRow(i);
			map=list.get(i);
			//遍历map对象,把每一个map中value对方到单元格中
			Set<String> set=map.keySet();
			Iterator<String> iterator=set.iterator();
			int j=0;
			while(iterator.hasNext()){
				String key=iterator.next();
				XSSFCell cell=rows.createCell(j);
				cell.setCellValue(map.get(key));//赋值
				j++;
			}
		}
		//创建流
		FileOutputStream out=new FileOutputStream(new File(fileName));
		//写到本地文件中
		xlsx.write(out);
		xlsx.close();
		out.flush();
		out.close();
		return true;
	}

编写测试类,验证是否正确

package com.demo;
 
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
 
import com.util.ExcelUtil;
/**
 * 测试类
 * @author NO-1
 *
 */
public class Test {
	public static void main(String[] args) {
//		String filename="C:/Users/NO-1/Documents/部门.xlsx";
		String filename="C:/Users/NO-1/Documents/xmndcS15090700001-2015-11-27.xls";
		String filename2="C:/Users/NO-1/Documents/2015-12-03.xls";
		try {
//			List<HashMap<String,String>> list=ExcelUtil.readExcel(filename);
//			for(HashMap<String,String> map:list){
//				System.out.println(map);
//			}
			List<HashMap<String,String>> list=new ArrayList<HashMap<String,String>>();
			HashMap<String,String> map=null;
			for(int i=0;i<10;i++){
				map=new HashMap<String,String>();
				for(int j=0;j<10;j++){
					map.put("测试"+j, "测试"+j);
				}
				list.add(map);
			}
			boolean isok=ExcelUtil.createExcel(filename2,list);
			if(isok){
				System.out.println("创建成功");
			}else{
				System.out.println("创建失败");
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}