Excel文件导入导出(1)

Excel文件导入导出操作(HSSF):

需要导入Apache提供的poi.jar包

Excel文件的组织形式：一个Excel文件对应多个workbook(文档对象：HSSFWorkbook)，一个workbook可以由多个sheet(表单：HSSFSheet)组成,一个sheet可以由多个row(列：HSSFRow)组成，一个row可以由多个col组成(行：HSSFCol)组成。

其它对象:HSSFFont(Excel字体)，HSSFDateFormat(日期格式)等等....

下面是通过将从数据库中查询出来的数据(存放在List<simune>中)导出生成Excel表格。

ExcelExport导出代码(需要传入到参数是：Excel文件路径及文件名，sheet表单名，List传入表格的数据):

其中colWidth[]表示表头中每一列表格的宽度，colInfo[]:为表头的内容信息:

其中ArrayUtils.isNotBlank等是我自己写的判断list等是否为空的工具类。

package com.xuzengqiang.tool.common.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.xuzengqiang.tool.domain.simune.Simune;
import com.xuzengqiang.tool.service.simune.SimuneService;
import com.xuzengqiang.tool.service.simune.impl.SimuneServiceImpl;

@SuppressWarnings("all")
public abstract class ExcelExportUtils<T> 
{

	private static HSSFWorkbook wb;
	private static File excelFile;
	// 表头宽度信息初始化
	private static int[] colWidth;
	// 表头标题
	private static String[] colInfo;

	public static void setColWidth(int[] colWidth) 
	{
		ExcelExportUtils.colWidth = colWidth;
	}

	public static void setColInfo(String[] colInfo) 
	{
		ExcelExportUtils.colInfo = colInfo;
	}

	/*
	 * 创建Excel文件 filePath:为Excel文件存放路径 fileName:为Excel文件名
	 */
	public void createExcelFile(String filePath, String fileName) 
	{
		File file = new File(filePath);
		if (!file.exists()) 
		{
			file.mkdirs();
		}
		excelFile = new File(filePath + File.separator + fileName);
		try 
		{
			if (!excelFile.exists()) 
			{
				excelFile.createNewFile();
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}

	}

	/**
	 * 初始化表格信息和样式
	 * 
	 * @param wb
	 * @param sheet
	 */
	public static void initTable(HSSFSheet sheet) 
	{
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-mm-dd"));
		// //设置日期格式
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置文本居中
		cellStyle.setFillForegroundColor((short) 5);// 设置背景色
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格

		HSSFRow row = sheet.createRow(0);
		// 设置表格的头部标题的每一列的宽度
		for (int i = 1; i <= 6; i++) 
		{
			sheet.setColumnWidth(i, colWidth[i - 1]);
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(colInfo[i - 1]);
		}

	}

	/**
	 * ExcelExport导出
	 * 
	 * @param filePath
	 *            :Excel文件保存路径
	 * @param fileName
	 *            :Excel文件名称
	 * @param sheetName
	 *            :表单名称
	 * @param list
	 *            :往表格中添加的数据
	 */
	public void excelExport(String filePath, String fileName, String sheetName, List<T> list) 
	{
		// Excel文件的创建
		createExcelFile(filePath, fileName);
		wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName);
		FileOutputStream fos = null;
		// 初始化表格
		initTable(sheet);

		if (ArrayUtils.listNoBlank(list)) 
		{
			for (int i = 0; i < list.size(); i++) 
			{
				HSSFRow row = sheet.createRow(i + 1);
				// 记录数
				row.createCell(0).setCellValue(i + 1);
				T t = list.get(i);
				rowMapper(t, row);
			}
		}

		try 
		{
			fos = new FileOutputStream(excelFile);
			wb.write(fos);
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		} 
		finally 
		{
			try 
			{
				if (fos != null) 
				{
					fos.close();
				}
			}
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		}
	}

	// 行映射
	public abstract void rowMapper(T t, HSSFRow row);

}

调用的main类:

package com.xuzengqiang.tool.web.struts.simune;

import java.util.LinkedHashMap;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;

import com.xuzengqiang.tool.common.utils.ExcelExportUtils;
import com.xuzengqiang.tool.domain.simune.Simune;
import com.xuzengqiang.tool.service.simune.SimuneService;
import com.xuzengqiang.tool.service.simune.impl.SimuneServiceImpl;

@SuppressWarnings("all")
public class ExcelExport extends ExcelExportUtils<Simune> 
{

	// 初始化表头每一列的宽度
	private static int[] colWidth = new int[] { 4000, 11000, 8000, 3000, 2000, 11000 };

	// 表格标题头的名称
	private static String[] colInfo = new String[] { "danbanIp", "simuneName", "instanceName", "port", "statut", "neRunStatus" };

	// 没有利用XML文件实现依赖注入，直接new了一个，不推荐
	private static SimuneService simuneService = new SimuneServiceImpl();

	public void setSimuneService(SimuneService simuneService) 
	{
		this.simuneService = simuneService;
	}

	public static void main(String[] args) 
	{
		ExcelExport export = new ExcelExport();

		ExcelExportUtils.setColWidth(colWidth);
		ExcelExportUtils.setColInfo(colInfo);
		// 根据端口号从小到大排序
		LinkedHashMap<String, String> orderByMap = new LinkedHashMap<String, String>();
		orderByMap.put("port", "desc");
		// list为数据库中查询出来的数据
		List<Simune> list = simuneService.findSimuneByDanbanIp("10.71.16.115", orderByMap);
		String filePath = "data";
		String fileName = "simune.xls";
		String sheetName = "Simune相关数据";
		// 主要方法
		export.excelExport(filePath, fileName, sheetName, list);
	}

	// 行映射:为每一行的每一列赋值
	@Override
	public void rowMapper(Simune simune, HSSFRow row) 
	{
		row.createCell(1).setCellValue(simune.getDanbanIp());
		row.createCell(2).setCellValue(simune.getSimuneName());
		row.createCell(3).setCellValue(simune.getInstanceName());
		row.createCell(4).setCellValue(simune.getPort());
		row.createCell(5).setCellValue(simune.getStatut());
		row.createCell(6).setCellValue(simune.getNeRunStatus());
	}

}

Excel文件导入：

通过InputStream对象获取HSSFWorkbook对象:

HSSFWorkbook wb=new HSSFWorkbook(InputStream对象);

通过HSSFWorkbook文档对象获取对应的表单对象

//通过文档对象获取表单对象的个数:
int sheetNum=wb.getNumberOfSheets();
//获取不同位置的表单对象
HSSFSheet sheet=wb.getSheetAt(index);

等等.....

ExcelImportUtils:

package com.xuzengqiang.tool.common.utils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

@SuppressWarnings("all")
public abstract class ExcelImportUtils<T> 
{

	private static HSSFWorkbook wb;

	/**
	 * ExcelExport导入
	 * 
	 * @param filePath
	 *            :Excel文件保存路径
	 * @param fileName
	 *            :Excel文件名称
	 */
	public List<T> excelImport(String filePath, String fileName) 
	{
		List<T> list = null;
		T t = null;
		InputStream is = null;
		BufferedReader br = null;
		try 
		{
			File excelFile = new File(filePath + File.separator + fileName);
			if (!excelFile.exists()) 
			{
				System.out.println("好吧,文件路径不存在！导入失败......");
				return null;
			}
			is = new FileInputStream(excelFile);
			wb = new HSSFWorkbook(is);
			// 获取对应的sheet的个数
			// System.out.println(wb.getNumberOfSheets());
			HSSFSheet sheet = wb.getSheetAt(0);

			list = new ArrayList<T>();
			// 直接从第二行开始，
			for (int i = 1; i < sheet.getLastRowNum(); i++)
			{
				HSSFRow row = sheet.getRow(i);
				t = rowMapper(row);
				list.add(t);
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		} 
		finally 
		{
			try 
			{
				if (br != null) 
				{
					br.close();
				}
				if (is != null) 
				{
					is.close();
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		}
		return list;
	}

	public abstract T rowMapper(HSSFRow row);

}

操作ExcelImport的main类

package com.xuzengqiang.tool.web.struts.simune;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;

import com.xuzengqiang.tool.common.utils.ExcelImportUtils;
import com.xuzengqiang.tool.domain.simune.Simune;

public class ExcelImport extends ExcelImportUtils<Simune> 
{

	public static void main(String[] args) 
	{
		ExcelImport excel = new ExcelImport();
		String filePath = "data";
		String fileName = "simune.xls";
		List<Simune> list = excel.excelImport(filePath, fileName);
		for (Simune simune : list) 
		{
			System.out.println(simune);
		}

	}

	//行映射：获取每一行的数据初始化领域对象Simune的值
	@Override
	public Simune rowMapper(HSSFRow row) 
	{
		Simune simune = new Simune();
		// 获取当前一行的总列数
		simune.setDanbanIp(row.getCell(1).toString());
		simune.setSimuneName(row.getCell(2).toString());
		simune.setInstanceName(row.getCell(3).toString());

		String port = row.getCell(4).toString();
		port = port.substring(0, port.lastIndexOf("."));
		simune.setPort(Integer.valueOf(port));

		String statut = row.getCell(5).toString();
		statut = statut.substring(0, statut.lastIndexOf("."));
		simune.setStatut(Integer.valueOf(statut));

		simune.setNeRunStatus(row.getCell(6).toString());
		return simune;
	}

}