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;
}
}