java将数据写入excel_java 写入数据到Excel文件中_Demo

importcom.google.common.collect.Lists;importcom.google.common.collect.Maps;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importjava.io.File;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.OutputStream;importjava.text.SimpleDateFormat;import java.util.*;

public classWriterExcelUtil {private static final Logger LOGGER = LoggerFactory.getLogger(WriterExcelUtil.class.getName());public static voidmain(String[] args) {

String path= "E://demo.xlsx";

String name= "test";

List titles =Lists.newArrayList();

titles.add("id");

titles.add("name");

titles.add("age");

titles.add("birthday");

titles.add("gender");

titles.add("date");

List> values =Lists.newArrayList();for (int i = 0; i < 10; i++) {

Map map =Maps.newHashMap();

map.put("id", i +1D);

map.put("name", "test_" +i);

map.put("age", i * 1.5);

map.put("gender", "man");

map.put("birthday", newDate());

map.put("date", Calendar.getInstance());

values.add(map);

}

System.out.println(writerExcel(path, name, titles, values));

}/*** 数据写入Excel文件

*

*@parampath 文件路径，包含文件全名，例如：D://file//demo.xls

*@paramname sheet名称

*@paramtitles 行标题列

*@paramvalues 数据集合，key为标题，value为数据

*@returnTrue\False*/

public static boolean writerExcel(String path, String name, List titles, List>values) {

LOGGER.info("path : {}", path);

String style= path.substring(path.lastIndexOf("."), path.length()).toUpperCase(); //从文件路径中获取文件的类型

returngenerateWorkbook(path, name, style, titles, values);

}/*** 将数据写入指定path下的Excel文件中

*

*@parampath 文件存储路径

*@paramname sheet名

*@paramstyle Excel类型

*@paramtitles 标题串

*@paramvalues 内容集

*@returnTrue\False*/

private static boolean generateWorkbook(String path, String name, String style, List titles, List>values) {

LOGGER.info("file style : {}", style);

Workbook workbook;if ("XLS".equals(style.toUpperCase())) {

workbook= newHSSFWorkbook();

}else{

workbook= newXSSFWorkbook();

}//生成一个表格

Sheet sheet;if (null == name || "".equals(name)) {

sheet= workbook.createSheet(); //name 为空则使用默认值

} else{

sheet=workbook.createSheet(name);

}//设置表格默认列宽度为15个字节

sheet.setDefaultColumnWidth((short) 15);//生成样式

Map styles =createStyles(workbook);/** 创建标题行*/Row row= sheet.createRow(0);//存储标题在Excel文件中的序号

Map titleOrder =Maps.newHashMap();for (int i = 0; i < titles.size(); i++) {

Cell cell=row.createCell(i);

cell.setCellStyle(styles.get("header"));

String title=titles.get(i);

cell.setCellValue(title);

titleOrder.put(title, i);

}/** 写入正文*/Iterator> iterator =values.iterator();int index = 0; //行号

while(iterator.hasNext()) {

index++; //出去标题行，从第一行开始写

row =sheet.createRow(index);

Map value =iterator.next();for (Map.Entrymap : value.entrySet()) {//获取列名

String title =map.getKey();//根据列名获取序号

int i =titleOrder.get(title);//在指定序号处创建cell

Cell cell =row.createCell(i);//设置cell的样式

if (index % 2 == 1) {

cell.setCellStyle(styles.get("cellA"));

}else{

cell.setCellStyle(styles.get("cellB"));

}//获取列的值

Object object =map.getValue();//判断object的类型

SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");if (object instanceofDouble) {

cell.setCellValue((Double) object);

}else if (object instanceofDate) {

String time=simpleDateFormat.format((Date) object);

cell.setCellValue(time);

}else if (object instanceofCalendar) {

Calendar calendar=(Calendar) object;

String time=simpleDateFormat.format(calendar.getTime());

cell.setCellValue(time);

}else if (object instanceofBoolean) {

cell.setCellValue((Boolean) object);

}else{

cell.setCellValue(object.toString());

}

}

}/** 写入到文件中*/

boolean isCorrect = false;try{

File file= newFile(path);

OutputStream outputStream= newFileOutputStream(file);

workbook.write(outputStream);

outputStream.close();

isCorrect= true;

}catch(IOException e) {

isCorrect= false;

LOGGER.error("write Excel file error : {}", e.getMessage());

}try{

workbook.close();

}catch(IOException e) {

isCorrect= false;

LOGGER.error("workbook closed error : {}", e.getMessage());

}returnisCorrect;

}/*** Create a library of cell styles*/

/***@paramwb

*@return

*/

private static MapcreateStyles(Workbook wb) {

Map styles =Maps.newHashMap();//标题样式

CellStyle titleStyle =wb.createCellStyle();

titleStyle.setAlignment(HorizontalAlignment.CENTER);//水平对齐

titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐

titleStyle.setLocked(true); //样式锁定

titleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());

Font titleFont=wb.createFont();

titleFont.setFontHeightInPoints((short) 16);

titleFont.setBold(true);

titleFont.setFontName("微软雅黑");

titleStyle.setFont(titleFont);

styles.put("title", titleStyle);//文件头样式

CellStyle headerStyle =wb.createCellStyle();

headerStyle.setAlignment(HorizontalAlignment.CENTER);

headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());//前景色

headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //颜色填充方式

headerStyle.setWrapText(true);

headerStyle.setBorderRight(BorderStyle.THIN);//设置边界

headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

headerStyle.setBorderLeft(BorderStyle.THIN);

headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());

headerStyle.setBorderTop(BorderStyle.THIN);

headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

headerStyle.setBorderBottom(BorderStyle.THIN);

headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

Font headerFont=wb.createFont();

headerFont.setFontHeightInPoints((short) 12);

headerFont.setColor(IndexedColors.WHITE.getIndex());

titleFont.setFontName("微软雅黑");

headerStyle.setFont(headerFont);

styles.put("header", headerStyle);

Font cellStyleFont=wb.createFont();

cellStyleFont.setFontHeightInPoints((short) 12);

cellStyleFont.setColor(IndexedColors.BLUE_GREY.getIndex());

cellStyleFont.setFontName("微软雅黑");//正文样式A

CellStyle cellStyleA =wb.createCellStyle();

cellStyleA.setAlignment(HorizontalAlignment.CENTER);//居中设置

cellStyleA.setVerticalAlignment(VerticalAlignment.CENTER);

cellStyleA.setWrapText(true);

cellStyleA.setBorderRight(BorderStyle.THIN);

cellStyleA.setRightBorderColor(IndexedColors.BLACK.getIndex());

cellStyleA.setBorderLeft(BorderStyle.THIN);

cellStyleA.setLeftBorderColor(IndexedColors.BLACK.getIndex());

cellStyleA.setBorderTop(BorderStyle.THIN);

cellStyleA.setTopBorderColor(IndexedColors.BLACK.getIndex());

cellStyleA.setBorderBottom(BorderStyle.THIN);

cellStyleA.setBottomBorderColor(IndexedColors.BLACK.getIndex());

cellStyleA.setFont(cellStyleFont);

styles.put("cellA", cellStyleA);//正文样式B:添加前景色为浅黄色

CellStyle cellStyleB =wb.createCellStyle();

cellStyleB.setAlignment(HorizontalAlignment.CENTER);

cellStyleB.setVerticalAlignment(VerticalAlignment.CENTER);

cellStyleB.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());

cellStyleB.setFillPattern(FillPatternType.SOLID_FOREGROUND);

cellStyleB.setWrapText(true);

cellStyleB.setBorderRight(BorderStyle.THIN);

cellStyleB.setRightBorderColor(IndexedColors.BLACK.getIndex());

cellStyleB.setBorderLeft(BorderStyle.THIN);

cellStyleB.setLeftBorderColor(IndexedColors.BLACK.getIndex());

cellStyleB.setBorderTop(BorderStyle.THIN);

cellStyleB.setTopBorderColor(IndexedColors.BLACK.getIndex());

cellStyleB.setBorderBottom(BorderStyle.THIN);

cellStyleB.setBottomBorderColor(IndexedColors.BLACK.getIndex());

cellStyleB.setFont(cellStyleFont);

styles.put("cellB", cellStyleB);returnstyles;

}

}