import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; /**
*
* @Description :POI读取/创建Excel文件
* @author : James
* @version : 1.0
* @Date : 2016年3月2日 下午2:18:27
*/
public class ExcelManager { private HSSFWorkbook hssfWorkbook;
private XSSFWorkbook xssfWorkbook; /**
* 读取Excel2010版
*
* @param inputStream输入流
* @return
*/
public List<String[]> readXlsx(InputStream inputStream) {
List<String[]> result = new ArrayList<>();
try {
xssfWorkbook = new XSSFWorkbook(inputStream);
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
String[] row = new String[xssfSheet.getRow(1).getLastCellNum()]; // 以第一列值行为标准
for (int i = 0; i < row.length; i++) {
row[i] = getValue(xssfRow.getCell(i));
}
result.add(row);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
} /**
* 读取Excel2003/2007版
*
* @param inputStream输入流
* @return
*/
public List<String[]> readXls(InputStream inputStream) {
List<String[]> result = new ArrayList<>();
try {
hssfWorkbook = new HSSFWorkbook(inputStream);
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
String[] row = new String[hssfSheet.getRow(1).getLastCellNum()];
for (int i = 0; i < row.length; i++) {
row[i] = getValue(hssfRow.getCell(i));
}
result.add(row);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
} public List<String[]> readXlsx(File file) {
List<String[]> result = new ArrayList<>();
try {
xssfWorkbook = new XSSFWorkbook(file);
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
String[] row = new String[xssfSheet.getRow(1).getLastCellNum()]; // 以第一列值行为标准
for (int i = 0; i < row.length; i++) {
row[i] = getValue(xssfRow.getCell(i));
}
result.add(row);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
} private String getValue(XSSFCell xssfRow) {
if (xssfRow == null)
return "";
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue().trim());
}
} private String getValue(HSSFCell hssfCell) {
if (hssfCell == null)
return "";
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue().trim());
}
} /**
* 重新创建新Excel文件
*
* @return
*/
public String createNewExcel(File file, String newFilePath, String[] title) {
try {
InputStream inputStream = new FileInputStream(file);
// 文件类型
String fileType = file.getName().substring(file.getName().lastIndexOf(".") + 1, file.getName().length());
List<String[]> objList = null;
if ("xlsx".equals(fileType)) {
objList = readXlsx(inputStream);
} else if ("xls".equals(fileType)) {
objList = readXls(inputStream);
}
// 创建Excel的工作书册 Workbook,对应到一个excel文档
XSSFWorkbook wb = new XSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
XSSFSheet sheet = wb.createSheet("sheet1");
sheet.setColumnWidth(0, 5500);// 设置单元格宽度，这里设置第一列的宽度
XSSFRow firstRow = sheet.createRow(0);
// 设置字体
XSSFFont font = wb.createFont();
font.setBold(true);// 粗体字
font.setColor(HSSFColor.RED.index);
font.setFontName("宋体");
// 设置单元格属性
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);// 设置字体
cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 生成表头
for (int i = 0; i < title.length; i++) {
XSSFCell cell = firstRow.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(cellStyle);// 设置单元格背景
}
// 生成数据
for (int i = 0; i < objList.size(); i++) {
String[] lineObj = objList.get(i);
XSSFRow row = sheet.createRow(i + 1);// 因为表头已经占用了第一行，所以后面生成的行需从第二行开始
for (int j = 0; j < lineObj.length; j++) {
XSSFCell cell = row.createCell(j);
cell.setCellValue(lineObj[j].toString());
}
}
FileOutputStream os = new FileOutputStream(newFilePath);
wb.write(os);
os.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
} public static void main(String[] args) {
String[] title = { "姓名", "年龄", "性别", "岗位", "入职年份" };
ExcelManager em = new ExcelManager();
em.createNewExcel(new File("D:\\用户信息.xlsx"), "D:\\new_excel.xlsx", title);
} }
效果图
原Excel文件：
生成后：