简单excel生成

不念不忘少年蓝@ 2023-02-18 01:46 29阅读 0赞

package DownTest;
    
    import java.io.FileOutputStream;
    import java.text.SimpleDateFormat;
    import java.util.Calendar;
    import java.util.Date;
    
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    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.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;
    
    public class WorkNotes { 
    	public static void main(String[] args) throws Exception { 
    
    		// 1.序号 2.日期 星期 3.工作内容 4.备注
    
    		String[] fileZHName = {  "序号", "日期", "星期", "工作内容", "备注" };
    		// 生成excel文件的头 .xlsx
    		Workbook hssfWorkbook = new XSSFWorkbook();
    
    		Sheet sheet = hssfWorkbook.createSheet("sheet1");
    		// 表头
    
    		CellStyle styleHead = getColumnTopStyle(hssfWorkbook);
    		CellStyle stylebody = getStyle(hssfWorkbook);
    
    		// 标题行
    		Row hdTitle = sheet.createRow(0);
    		// hdTitle.setHeight((short) (25 * 20));
    		hdTitle.setHeight((short) (15.625 * 40));// 行高
    		Cell cellTiTle = hdTitle.createCell(0);
    		// hdTitle.setHeight((short) (15.625*40));
    
    		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, fileZHName.length - 1));
    
    		cellTiTle.setCellValue("我的本周日志");
    		cellTiTle.setCellStyle(styleHead);
    
    		// 列标题行
    		Row headRow = sheet.createRow(1);
    		headRow.setHeight((short) (14.625 * 40));// 行高
    		for (int i = 0; i < fileZHName.length; i++) { 
    			Cell cell1 = headRow.createCell(i);
    			cell1.setCellValue(fileZHName[i]);
    			cell1.setCellStyle(styleHead);
    			// int length = filename.getBytes().length;
    			if ("工作内容".equals(fileZHName[i])) { 
    				sheet.setColumnWidth(i, 80 * 256);
    			} else { 
    				sheet.setColumnWidth(i, 20 * 256);
    			}
    
    			// sheet.setColumnWidth(1,length*256/2 );
    		}
    
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    		Calendar cal = Calendar.getInstance();
    		// 设置一个星期的第一天，按中国的习惯一个星期的第一天是星期一
    		cal.setFirstDayOfWeek(Calendar.MONDAY);
    		int weekD = cal.get(Calendar.DAY_OF_WEEK) - 1;
    
    		if (weekD > 1) { 
    			cal.add(Calendar.DATE, (1 - weekD));
    		}
    		Date mondayDate1 = cal.getTime();
    		String sti = sdf.format(mondayDate1);
    		String[] week = {  "周一", "周二", "周三", "周四", "周五", "周六", "周日" };
    		// 获得当前日期是一个星期的第几天
    		String eti = "";
    		for (int i = 1; i < 8; i++) { 
    			Row dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
    			dataRow.setHeight((short) (14.625 * 40));// 行高
    			// int dayWeek = cal.get(Calendar.DAY_OF_WEEK);
    			if (i != 1) { 
    				cal.add(Calendar.DATE, 1);
    			}
    
    			Date mondayDate = cal.getTime();
    			String weekBegin = sdf.format(mondayDate);
    			for (int j = 0; j < 5; j++) { 
    				Cell cell1 = dataRow.createCell(j);
    				cell1.setCellStyle(stylebody);
    
    				if (j == 0) { 
    					cell1.setCellValue(i);
    				} else if (j == 1) { 
    
    					cell1.setCellValue(weekBegin);
    				} else if (j == 2) { 
    					// cell1.setCellStyle(arg0);
    					cell1.setCellValue(week[i - 1]);
    
    				} else { 
    					cell1.setCellValue("");
    				}
    
    			}
    			if (i == 7) { 
    				eti = weekBegin;
    			}
    		}
    
    		String name = "工作日志 " + sti + "-" + eti;
    
    		FileOutputStream fout = new FileOutputStream("C:\\Users\\zys2042\\Desktop\\工作日志\\" + name + ".xlsx");
    
    		hssfWorkbook.write(fout);
    		fout.close();
    
    	}
    
    	public static CellStyle getColumnTopStyle(Workbook workbook) { 
    
    		// 设置字体
    		Font font = workbook.createFont();
    		// 设置字体大小
    		font.setFontHeightInPoints((short) 12);
    		// 字体加粗
    		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    		// 设置字体名字
    		font.setFontName("Courier New");
    		font.setColor(HSSFColor.RED.index);
    
    		// int length = str.getBytes().length;
    		// sheet.setColumnWidth((short)1,(short)(length*256));
    		// 设置样式;
    		CellStyle style = workbook.createCellStyle();
    		// 设置底边框;
    		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    		// 设置底边框颜色;
    		style.setBottomBorderColor(HSSFColor.BLUE.index);
    		// 设置左边框;
    		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    		// 设置左边框颜色;
    		style.setLeftBorderColor(HSSFColor.BLUE.index);
    		// 设置右边框;
    
    		style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色;
    		style.setRightBorderColor(HSSFColor.BLUE.index); // 设置顶边框;
    		style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色;
    		style.setTopBorderColor(HSSFColor.BLUE.index);
    
    		// 在样式用应用设置的字体;
    		style.setFont(font);
    		// 设置自动换行;
    		style.setWrapText(true);
    		// 设置水平对齐的样式为居中对齐;
    		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		// 设置垂直对齐的样式为居中对齐;
    		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
    		return style;
    
    	}
    
    	/*
    	 * 列数据信息单元格样式
    	 */
    	public static CellStyle getStyle(Workbook workbook) { 
    		// 设置字体
    		Font font = workbook.createFont();
    		// 设置字体大小
    		font.setFontHeightInPoints((short) 11);
    		// 字体加粗
    		// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    		// 设置字体名字
    		font.setFontName("Courier New");
    		// 设置样式;
    		CellStyle style = workbook.createCellStyle();
    
    		// 设置底边框;
    		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置底边框颜色;
    		style.setBottomBorderColor(HSSFColor.BLUE.index); // 设置左边框;
    		style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置左边框颜色;
    		style.setLeftBorderColor(HSSFColor.BLUE.index); // 设置右边框;
    		style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框颜色;
    		style.setRightBorderColor(HSSFColor.BLUE.index); // 设置顶边框;
    		style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置顶边框颜色;
    		style.setTopBorderColor(HSSFColor.BLUE.index);
    
    		// 在样式用应用设置的字体;
    		style.setFont(font);
    		// 设置自动换行;
    		style.setWrapText(true);
    		// 设置水平对齐的样式为居中对齐;
    		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		// 设置垂直对齐的样式为居中对齐;
    		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
    		return style;
    
    	}
    
    }

所需jar包:  
https://mvnrepository.com/:  
poi-3.12.jar  
poi-ooxml-3.12.jar  
poi-ooxml-schemas-3.12.jar  
xmlbeans-2.3.0.jar