public class ExcelUtils {
private static final String FILE_PATH= "d:/tmp";
public static void main(String[] args) {
int columnNumber = 6;
int[] columnWidth = {20, 20, 20, 20, 20, 20};
String titleName = "测试";
String[] columnName = {"评审项目", "评审要点", "分值", "评审方法", "评审结果及扣分原因", "得分"};
String[][] dataList= {{"物理安全","二", "三1","四1", "五1", "六1"},
{"物理安全","二", "三1","四2", "五2", "六2"},
{"物理安全","二", "三","四3", "五3", "六3"}};
try {
String s = exportExcelAndMerge(columnNumber, columnWidth, titleName, columnName, dataList);
String s1 = exportExcelAndMerge(columnNumber, columnWidth, titleName, columnName, dataList);
} catch (Exception e) {
e.printStackTrace();
}
}
public static List<String[]> getExcel(FileInputStream fileInputStream, int beginRow, int endRow, int endColl) throws Exception{
List<String[]> list = new ArrayList<String[]>();
XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = wb.getSheetAt(0);
if(endRow == 0 ) {
endRow = sheet.getLastRowNum();
}
for (int i = beginRow; i <= endRow; i++) {
XSSFRow row = sheet.getRow(i);
String[] cells = new String[endColl];
for (int j = 0; j < endColl; j++) {
XSSFCell cell = row.getCell(j);
try {
int type = cell.getCellType();
if(type==1) {
cells[j] = cell.getStringCellValue();
}else if(type==0) {
cells[j] = String.valueOf(cell.getNumericCellValue());
if(cells[j].substring(cells[j].length()-2, cells[j].length()).equals(".0")) {
cells[j] = cells[j].substring(0,cells[j].length()-2);
}
}else {
cells[j] = "";
}
} catch (Exception e) {
cells[j] = "";
}
}
list.add(cells);
}
fileInputStream.close();
return list;
}
public static List<String[]> getExcel(FileInputStream fileInputStream,int[] columnNumber,int beginRow,int endRow) throws Exception{
List<String[]> list = new ArrayList<String[]>();
HSSFWorkbook wb = new HSSFWorkbook(fileInputStream);
HSSFSheet sheet = wb.getSheetAt(0);
if(endRow == 0 ) {
endRow = sheet.getLastRowNum();
}
for (int i = beginRow; i <= endRow; i++) {
HSSFRow row = sheet.getRow(i);
String[] cells = new String[columnNumber.length];
for (int j = 0; j < columnNumber.length; j++) {
HSSFCell cell = row.getCell(columnNumber[j]);
try {
int type = cell.getCellType();
if(type==1) {
cells[j] = cell.getStringCellValue();
}else if(type==0) {
cells[j] = String.valueOf(cell.getNumericCellValue());
if(cells[j].substring(cells[j].length()-2, cells[j].length()).equals(".0")) {
cells[j] = cells[j].substring(0,cells[j].length()-2);
}
}else {
cells[j] = "";
}
} catch (Exception e) {
cells[j] = "";
}
}
list.add(cells);
}
return list;
}
public static String ExportExcel(Integer columnNumber,int[] columnWidth,String titleName,String[] columnName,String[][] dataList) throws Exception{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheetname");
for (int i = 0; i < columnNumber; i++){
sheet.setColumnWidth(i, columnWidth[i] * 270);
}
HSSFRow row1 = sheet.createRow((int) 0);
row1.setHeightInPoints(45);
HSSFFont fontStyle2 = getFontStyle(wb, true, "宋体", (short)15);
HSSFCellStyle style2 = getHeadCellStyle(wb, fontStyle2);
HSSFCell cell1 = row1.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnNumber - 1));
cell1.setCellValue(titleName);
cell1.setCellStyle(style2);
HSSFRow row = sheet.createRow((int) 1);
row.setHeightInPoints(37);
HSSFFont fontStyle = getFontStyle(wb, true, "宋体", (short)13);
HSSFCellStyle style = getHeadCellStyle(wb, fontStyle);
for (int i = 0; i < columnNumber; i++){
HSSFCell cell = row.createCell(i);
cell.setCellValue(columnName[i]);
cell.setCellStyle(style);
}
HSSFCellStyle cellStyle = getCellStyle(wb, null);
for(int i = 0; i < dataList.length; i++){
row = sheet.createRow((int) i + 2);
row.setHeightInPoints(20);
HSSFCell datacell = null;
for (int j = 0; j < columnNumber; j++){
datacell = row.createCell(j);
datacell.setCellValue(dataList[i][j]);
datacell.setCellStyle(cellStyle);
}
}
String filePath = saveFile(wb,titleName);
return filePath;
}
public static String exportExcelAndMerge(int columnNumber,int[] columnWidth,String titleName,String[] columnName,Object[][] dataList) throws Exception{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheetName");
for (int i = 0; i < columnNumber; i++){
sheet.setColumnWidth(i, columnWidth[i] * 270);
}
HSSFRow row1 = sheet.createRow(0);
row1.setHeightInPoints(45);
HSSFFont fontStyle2 = getFontStyle(wb, true, "宋体", (short)15);
HSSFCellStyle style2 = getHeadCellStyle(wb, fontStyle2);
HSSFCell cell1 = row1.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnNumber - 1));
cell1.setCellValue(titleName);
cell1.setCellStyle(style2);
HSSFRow row = sheet.createRow((int) 1);
row.setHeightInPoints(37);
HSSFFont fontStyle = getFontStyle(wb, true, "宋体", (short)13);
HSSFCellStyle style = getHeadCellStyle(wb, fontStyle);
for (int i = 0; i < columnNumber; i++){
HSSFCell cell = row.createCell(i);
cell.setCellValue(columnName[i]);
cell.setCellStyle(style);
}
HSSFCellStyle cellStyle = getCellStyle(wb, null);
int[] k = new int[columnNumber];
for(int i = 0; i < dataList.length; i++){
row = sheet.createRow((int) i + 2);
row.setHeightInPoints(20);
HSSFCell datacell = null;
for (int j = 0; j < columnNumber; j++){
datacell = row.createCell(j);
datacell.setCellValue((String) dataList[i][j]);
datacell.setCellStyle(cellStyle);
if(i != 0){
boolean isEquel = dataList[i][j].equals(dataList[i-1][j]) && (! "".equals(dataList[i][j]));
if (isEquel) {
k[j] = k[j]+1;
}else{
if(k[j] > 0){
sheet.addMergedRegion(new CellRangeAddress(i+2-k[j]-1, i+2-1, j, j));
k[j] = 0;
}
}
if (i == dataList.length-1 ){
if(k[j] > 0){
sheet.addMergedRegion(new CellRangeAddress(i+2-k[j], i+2, j, j));
k[j] = 0;
}
}
}
}
}
String filePath = saveFile(wb,titleName);
return filePath;
}
private static HSSFCellStyle getHeadCellStyle(HSSFWorkbook wb, HSSFFont font) {
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
if(font!=null) {
style.setFont(font);
}
return style;
}
private static HSSFCellStyle getCellStyle(HSSFWorkbook wb, HSSFFont font) {
HSSFCellStyle style = wb.createCellStyle();
style.setWrapText(true);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
if(font!=null) {
style.setFont(font);
}
return style;
}
private static HSSFFont getFontStyle(HSSFWorkbook wb, boolean bold, String fontName, short fontSize) {
HSSFFont headerFont = (HSSFFont) wb.createFont();
if(bold) {
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
headerFont.setFontName(fontName);
headerFont.setFontHeightInPoints(fontSize);
return headerFont;
}
private static String saveFile(HSSFWorkbook wb, String fileName) throws Exception {
String filePath="";
Date createTime = new Date();
SimpleDateFormat forMater = new SimpleDateFormat("yyyyMMdd");
String path = "export" + File.separatorChar + forMater.format(createTime);
File targetFile = new File(FILE_PATH + File.separatorChar, path);
if (!targetFile.exists()) {
targetFile.mkdirs();
}
String fileStr = FILE_PATH + File.separatorChar + path + File.separatorChar +fileName;
filePath = fileStr+ ".xls";
File file=new File(filePath);
int i=1;
while(file.exists()) {
filePath=fileStr+i+".xls";
file=new File(filePath);
i++;
}
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
String str = "导出" + filePath + "成功!";
fout.close();
return filePath;
}
}
public class ListToTwoArray {
public static Object[][] ListToArray(List<LinkedHashMap<String, Object>> listMap, int keyLength) {
if (CollectionUtils.isEmpty(listMap)) {
return new Object[0][];
}
int size = listMap.size();
Object[][] array = new Object[size][keyLength];
List<List<String>> listList = new LinkedList<>();
for (LinkedHashMap<String, Object> linkedHashMap : listMap) {
List<String> list = new LinkedList<>();
for (Map.Entry entryMap : linkedHashMap.entrySet()) {
list.add(entryMap.getValue().toString());
}
listList.add(list);
}
try {
for (int i = 0; i < size; i++) {
for (int j = 0; j <keyLength ; j++) {
array[i][j] = listList.get(i).get(j);
}
}
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(Arrays.deepToString(array));
return array;
}
}