java 创建excel_如何使用Java创建Excel(.xls 和 .xlsx)文件 并写入数据

/****/

packagecom.nokia.jira.utils;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.FileOutputStream;importjava.io.IOException;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.Iterator;importjava.util.List;importjava.util.Map;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.hssf.util.HSSFColor;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.CellType;importorg.apache.poi.ss.usermodel.Color;importorg.apache.poi.ss.usermodel.HorizontalAlignment;importorg.apache.poi.ss.usermodel.VerticalAlignment;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;/***@authorbpan

*

* created 2018年2月27日*/

public classCreateExcelFile {private static HSSFWorkbook hWorkbook = null;private static XSSFWorkbook xWorkbook = null;/*** 判断文件是否存在.

*@paramfileDir 文件路径

*@return

*/

public static booleanfileExist(String fileDir){boolean flag = false;

File file= newFile(fileDir);

flag=file.exists();returnflag;

}/*** 判断文件的sheet是否存在.

*@paramfileDir 文件路径

*@paramsheetName 表格索引名

*@returnboolean*/

public static booleanXlsSheetExist(String fileDir, String sheetName){boolean flag = false;

File file= newFile(fileDir);if(file.exists()) {//文件存在，创建workbook

try{

hWorkbook= new HSSFWorkbook(newFileInputStream(file));

HSSFSheet sheet=hWorkbook.getSheet(sheetName);if (sheet!=null) {//文件存在，sheet存在

flag= true;

}

}catch(IOException e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}else{//文件不存在

flag = false;

}returnflag;

}/*** 创建新excel(xls).

*@paramfileDir excel的路径

*@paramsheetNames 要创建的表格索引列表

*@paramtitleRow excel的第一行即表格头*/

public static void createExcelXls(String fileDir, ListsheetNames, String titleRow[]){//创建workbook

hWorkbook = newHSSFWorkbook();//新建文件

FileOutputStream fileOutputStream = null;

HSSFRow row= null;try{

CellStyle cellStyle=hWorkbook.createCellStyle();

cellStyle.setAlignment(HorizontalAlignment.LEFT);

cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);//添加Worksheet(不添加sheet时生成的xls文件打开时会报错)

for(int i = 0; i

hWorkbook.createSheet(sheetNames.get(i));

hWorkbook.getSheet(sheetNames.get(i)).createRow(0);//添加表头, 创建第一行

row = hWorkbook.getSheet(sheetNames.get(i)).createRow(0);

row.setHeight((short)(20*20));for (short j = 0; j < titleRow.length; j++) {

HSSFCell cell=row.createCell(j, CellType.BLANK);

cell.setCellValue(titleRow[j]);

cell.setCellStyle(cellStyle);

}

fileOutputStream= newFileOutputStream(fileDir);

hWorkbook.write(fileOutputStream);

}

}catch(Exception e) {//TODO Auto-generated catch block

e.printStackTrace();

}finally{if (fileOutputStream != null) {try{

fileOutputStream.close();

}catch(IOException e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}/*** 删除文件.

*@paramfileDir 文件路径

*@return如果文件不存在返回false, 如果文件存在删除成功之后返回true*/

public static booleandeleteExcel(String fileDir) {boolean flag = false;

File file= newFile(fileDir);//判断目录或文件是否存在

if (!file.exists()) { //不存在返回 false

returnflag;

}else{//判断是否为文件

if (file.isFile()) { //为文件时调用删除文件方法

file.delete();

flag= true;

}

}returnflag;

}/*** 往excel(xls)中写入(已存在的数据无法写入).

*@paramfileDir 文件路径

*@paramsheetName 表格索引

*@paramobject

*@throwsException*/

public static void writeToExcelXls(String fileDir, String sheetName, List> mapList) throwsException{//创建workbook

File file = newFile(fileDir);try{

hWorkbook= new HSSFWorkbook(newFileInputStream(file));

}catch(FileNotFoundException e){

e.printStackTrace();

}catch(IOException e) {//TODO Auto-generated catch block

e.printStackTrace();

}//文件流

FileOutputStream fileOutputStream = null;

HSSFSheet sheet=hWorkbook.getSheet(sheetName);//获取表格的总行数//int rowCount = sheet.getLastRowNum() + 1;//需要加一//获取表头的列数

int columnCount = sheet.getRow(0).getLastCellNum();try{//获得表头行对象

HSSFRow titleRow = sheet.getRow(0);//创建单元格显示样式

CellStyle cellStyle =hWorkbook.createCellStyle();

cellStyle.setAlignment(HorizontalAlignment.LEFT);

cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);if(titleRow!=null){for(int rowId = 0; rowId < mapList.size(); rowId++){

Map map =mapList.get(rowId);

HSSFRow newRow=sheet.createRow(rowId+1);

newRow.setHeight((short)(20*20));//设置行高 基数为20

for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) { //遍历表头//trim()的方法是删除字符串中首尾的空格

String mapKey =titleRow.getCell(columnIndex).toString().trim();

HSSFCell cell=newRow.createCell(columnIndex);

cell.setCellStyle(cellStyle);

cell.setCellValue(map.get(mapKey)==null ? null: map.get(mapKey).toString());

}

}

}

fileOutputStream= newFileOutputStream(fileDir);

hWorkbook.write(fileOutputStream);

}catch(Exception e) {throwe;

}finally{try{if (fileOutputStream != null) {

fileOutputStream.close();

}

}catch(IOException e) {

e.printStackTrace();

}

}

}/*** 创建Excel(xlsx)

*@paramfileDir 文件名称及地址

*@paramsheetName sheet的名称

*@paramtitleRow 表头*/

public static voidcreateExcelXlsx(String fileDir, String sheetName, String titleRow[]){

}public static voidmain(String[] args) {

String fileDir= "d:\\workbook.xls";

List sheetName = new ArrayList<>();

sheetName.add("A");

sheetName.add("B");

sheetName.add("C");

System.out.println(sheetName);

String[] title= {"id","name","password"};

CreateExcelFile.createExcelXls(fileDir, sheetName, title);

List> userList1 = new ArrayList>();

Map map=new HashMap();

map.put("id", "111");

map.put("name", "张三");

map.put("password", "111！@#");

Map map2=new HashMap();

map2.put("id", "222");

map2.put("name", "李四");

map2.put("password", "222！@#");

Map map3=new HashMap();

map3.put("id", "33");

map3.put("name", "王五");

map3.put("password", "333！@#");

userList1.add(map);

userList1.add(map2);

userList1.add(map3);

Map>> users = new HashMap<>();

users.put("A", userList1);

List> userList2 = new ArrayList>();

Map map4=new HashMap();

map4.put("id", "111");

map4.put("name", "张三");

map4.put("password", "111！@#");

Map map5=new HashMap();

map5.put("id", "222");

map5.put("name", "李四");

map5.put("password", "222！@#");

Map map6=new HashMap();

map6.put("id", "33");

map6.put("name", "王五");

map6.put("password", "333！@#");

userList2.add(map4);

userList2.add(map5);

userList2.add(map6);

users.put("B", userList2);

List> userList3 = new ArrayList>();

users.put("C", userList3);

System.out.println(sheetName.size());//删除List 集合中特定的元素

for(Iterator sheeNameIterator =sheetName.iterator();sheeNameIterator.hasNext();){

String sheet=sheeNameIterator.next();if ( users.get(sheet).size() == 0) {

sheeNameIterator.remove();

}

}

System.out.println(sheetName.size());

createExcelXls(fileDir, sheetName, title);for (int j = 0; j < sheetName.size(); j++) {try{

writeToExcelXls(fileDir, sheetName.get(j), users.get(sheetName.get(j)));

}catch(Exception e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}