java 获取Excel文件内容和编写Excel文件实现方法

package com.HHS.www.example;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
*RWExcel类 导入导出Excel
* 
*/
public class RWExcel {
/**
* 获取Excel文件内容
* */
public static Map<String,List<Object>> read(String filePath) throws IOException {
String fileType = filePath.substring(filePath.lastIndexOf(".") + 1, filePath.length());
InputStream stream = new FileInputStream(filePath);
BufferedInputStream bufferedInputStream=new BufferedInputStream(stream);
Map<String,List<Object>>objMap=new LinkedHashMap<String, List<Object>>();
Workbook wb = null;
if (fileType.equals("xlsx")||fileType.equals("xls")||fileType.equals("et")||fileType.equals("ett")||fileType.equals("xlt")) {
wb = new XSSFWorkbook(bufferedInputStream);
for(int sheetnum=0;sheetnum<wb.getNumberOfSheets();sheetnum++){
Sheet sheet = wb.getSheetAt(sheetnum);
System.out.println(sheet.getSheetName());
for (Row row : sheet) {
List<Object>values=new ArrayList<Object>();
for (Cell cell : row) {
try {
values.add(cell.getStringCellValue());
} catch (IllegalStateException e) {
try {
values.add(String.valueOf(cell.getNumericCellValue()));
} catch (IllegalStateException ex) {
values.add(cell.getBooleanCellValue());
}
}
}
objMap.put(sheet.getSheetName()+"(object"+row.getRowNum()+")",values);
}
}
}  else {
System.out.println("您输入的excel格式不正确");
}
bufferedInputStream.close();
return objMap;
}
/**
* 编写Excel文件
* 其中Excel的第一行是标题、第二行是生成时间、第三行是字段名称。
* */
public static boolean write(String outPath,String sheetName,List<Map<String, Object>>values) throws IOException, ClassNotFoundException {
String fileType = outPath.substring(outPath.lastIndexOf(".") + 1, outPath.length());
// 创建工作文档对象
Workbook wb = null;
if (fileType.equals("xlsx")||fileType.equals("xls")||fileType.equals("et")||fileType.equals("ett")) {
wb = new XSSFWorkbook();
} else {
System.out.println("您的文档格式不正确！");
return false;
}
Cell cell =null;
//设置Excell样式
CellStyle cellstyle=null;
// 创建sheet对象
Sheet sheet = (Sheet) wb.createSheet(sheetName);
// 循环写入行数据
for(int i = 0; i < 2; i++){
Row row = (Row) sheet.createRow(i);
if(i==0){
cellstyle=wb.createCellStyle();
Font font=wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//字体样式设置
font.setFontHeightInPoints((short)16);//设置字体大小
cellstyle.setFont(font);//字体设置
cellstyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
CellRangeAddress region = new CellRangeAddress(i, i, (short) 0, (short) values.get(2).keySet().size()-1);
sheet.addMergedRegion(region);
}else if(i==1){
cellstyle=wb.createCellStyle();
Font font=wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//字体样式设置
cellstyle.setFillForegroundColor((short)HSSFColor.BRIGHT_GREEN.index);//单元格背景颜色设置
cellstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);//单元格背景设置
font.setFontHeightInPoints((short)8);
cellstyle.setFont(font);//字体设置
cellstyle.setWrapText(true);//自动换行
cellstyle.setAlignment(CellStyle.ALIGN_LEFT);
CellRangeAddress region = new CellRangeAddress(i, i, (short) 0, (short) values.get(2).keySet().size()-1);
sheet.addMergedRegion(region);
}
cellstyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
cellstyle.setBorderBottom(CellStyle.BORDER_MEDIUM);//下边框设置
cellstyle.setBorderLeft(CellStyle.BORDER_MEDIUM);//左边框设置
cellstyle.setBorderRight(CellStyle.BORDER_MEDIUM);//右边框设置
cellstyle.setBorderTop(CellStyle.BORDER_MEDIUM);//上边框设置
for(int a=0;a<values.get(2).keySet().size();a++){
cell = row.createCell(a);
cell.setCellStyle(cellstyle);
while(a==0){
for(String str:values.get(i).keySet()){
if(values.get(i).get(str).getClass().equals(Class.forName("java.lang.Integer"))){
cell.setCellValue((Integer)values.get(i).get(str));
}
if(values.get(i).get(str).getClass().equals(Class.forName("java.lang.String"))){
cell.setCellValue((String)values.get(i).get(str));
}
if(values.get(i).get(str).getClass().equals(Class.forName("java.util.Date"))){
cell.setCellValue((Date)values.get(i).get(str));
}
if(values.get(i).get(str).getClass().equals(Class.forName("org.apache.poi.ss.usermodel.RichTextString"))){
cell.setCellValue((RichTextString)values.get(i).get(str));
}
if(values.get(i).get(str).getClass().equals(Class.forName("java.util.Calendar"))){
cell.setCellValue((Calendar)values.get(i).get(str));
}
}
break;
}
}
}
for (int i = 2; i < values.size(); i++) {
Row row = (Row) sheet.createRow(i);
if(i==2){
Font font=wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//字体样式设置
cellstyle=wb.createCellStyle();
cellstyle.setWrapText(true);//自动换行
cellstyle.setFillForegroundColor((short)HSSFColor.LIGHT_BLUE.index);//单元格背景颜色设置
cellstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);//单元格背景设置
cellstyle.setFont(font);//字体设置
}else{
cellstyle=wb.createCellStyle();
}
cellstyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
cellstyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
cellstyle.setBorderBottom(CellStyle.BORDER_MEDIUM);//下边框设置
cellstyle.setBorderLeft(CellStyle.BORDER_MEDIUM);//左边框设置
cellstyle.setBorderRight(CellStyle.BORDER_MEDIUM);//右边框设置
cellstyle.setBorderTop(CellStyle.BORDER_MEDIUM);//上边框设置
int a=0;
// 循环写入列数据
for(String str:values.get(i).keySet()){
cell = row.createCell(a);
cell.setCellStyle(cellstyle);
if(values.get(i).get(str)!=null){
if(values.get(i).get(str).getClass().equals(Class.forName("java.lang.Integer"))){
cell.setCellValue((Integer)values.get(i).get(str));
}
if(values.get(i).get(str).getClass().equals(Class.forName("java.lang.String"))){
cell.setCellValue((String)values.get(i).get(str));
}
if(values.get(i).get(str).getClass().equals(Class.forName("java.util.Date"))){
cell.setCellValue((Date)values.get(i).get(str));
}
if(values.get(i).get(str).getClass().equals(Class.forName("org.apache.poi.ss.usermodel.RichTextString"))){
cell.setCellValue((RichTextString)values.get(i).get(str));
}
if(values.get(i).get(str).getClass().equals(Class.forName("java.util.Calendar"))){
cell.setCellValue((Calendar)values.get(i).get(str));
}
}
a++;
}
}
// 创建文件流
OutputStream stream = new FileOutputStream(outPath);
BufferedOutputStream bufferedOutputStream=new BufferedOutputStream(stream);
// 写入数据
wb.write(bufferedOutputStream);
// 关闭文件流
bufferedOutputStream.close();
return true;

}
public static void main(String[] args) {
//读取Excel文件测试
try {
for(Entry<String, List<Object>> entry:RWExcel.read("D:/示例文件Excel/示例文件Excel2.xls").entrySet()){
System.out.println(entry);

}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//编写Excel文件测试
try {
SimpleDateFormat sdf =   new SimpleDateFormat( " yyyy年MM月dd日 HH时mm分ss秒 " );
List<Map<String,Object>>values=new ArrayList<Map<String,Object>>();
Map<String,Object>objMap6=new LinkedHashMap<String, Object>();
objMap6.put("标题", "2016级员工信息表");
values.add(objMap6);
Map<String,Object>objMap7=new LinkedHashMap<String, Object>();
objMap7.put("生成时间", "2016级员工信息表生成于"+sdf.format(new Date()));
values.add(objMap7);
Map<String,Object>objMap1=new LinkedHashMap<String, Object>();
objMap1.put("name","姓名");
objMap1.put("age","年龄");
objMap1.put("sex","性别");
objMap1.put("married","婚姻状况（婚否）");
objMap1.put("name1","姓名");
objMap1.put("age1","年龄");
objMap1.put("sex1","性别");
objMap1.put("married1","婚姻状况（婚否）");
values.add(objMap1);
Map<String,Object>objMap=new LinkedHashMap<String, Object>();
objMap.put("name","小强");
objMap.put("age",18);
objMap.put("sex","男");
objMap.put("married","未婚");
objMap.put("name1","小强");
objMap.put("age1",18);
objMap.put("sex1","男");
objMap.put("married1","未婚");
values.add(objMap);
Map<String,Object>objMap2=new LinkedHashMap<String, Object>();
objMap2.put("name",null);
objMap2.put("age",18);
objMap2.put("sex","男");
objMap2.put("married","未婚");
objMap2.put("name1","小强");
objMap2.put("age1",18);
objMap2.put("sex1","男");
objMap2.put("married1","未婚");
values.add(objMap2);
try {
RWExcel.write("D:/示例文件Excel/示例文件Excel2.xls", "测试工作表", values);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}