import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
/**
*
* @ClassName: ExcelWriteForWeeklyPaper
* @Description: 周报导出
* @author yabo.liu
* @date 2019年8月23日 下午2:08:57
*
*/
public class ExcelWriteForWeeklyPaper {
public static void main(String[] args) {
//读取excel路径
String excelPath = "E:\\excel\\new-weekly-list.xlsx";
System.out.println("excel路径为："+excelPath);
//读取excel数据
JSONArray jsonArray = getExcelData1(excelPath);
//往excel里写数据，key需要跟sheet页的key相同，值为需要写的数据
writeWeeklyPaper(jsonArray);
}
public static JSONArray getExcelData1(String excelPath){
JSONArray jsonArray = new JSONArray();
try {
File excel = new File(excelPath);
if (excel.isFile() && excel.exists()) { //判断文件是否存在
String[] split = excel.getName().split("\\."); //.是特殊字符，需要转义！！！！！
Workbook wb;
//根据文件后缀（xls/xlsx）进行判断
if ( "xls".equals(split[1])){
FileInputStream fis = new FileInputStream(excel); //文件流对象
wb = new HSSFWorkbook(fis);
}else if ("xlsx".equals(split[1])){
wb = new XSSFWorkbook(excel);
}else {
System.out.println("文件类型错误!");
return null;
}
//开始解析
Sheet sheet = wb.getSheetAt(0);//读取sheet 0
int firstRowIndex = sheet.getFirstRowNum()+1;//第一行是列名，所以不读
int lastRowIndex = sheet.getLastRowNum();
for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {//遍历行
JSONObject jsonObject = new JSONObject();
Row row = sheet.getRow(rIndex);
if (row != null) {
int firstCellIndex = row.getFirstCellNum();
int lastCellIndex = row.getLastCellNum();
for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {//遍历列
Cell cell = row.getCell(cIndex);
if (cell != null) {
if (cIndex == 0) {
jsonObject.put("idNo",cell.toString());
}else if (cIndex == 1){
jsonObject.put("taskName",cell.toString());
}else if (cIndex == 2) {
jsonObject.put("taskStatus",cell.toString());
}else if (cIndex == 3) {
jsonObject.put("taskProgress",cell.toString());
}else if (cIndex == 4) {
jsonObject.put("lastTaskStatus",cell.toString());
}else if (cIndex == 5) {
jsonObject.put("lastTaskProgress",cell.toString());
}else if (cIndex == 6) {
jsonObject.put("head",cell.toString());
}
}
}
}
jsonArray.add(jsonObject);
}
} else {
System.out.println("找不到指定的文件");
}
} catch (Exception e) {
e.printStackTrace();
}
return jsonArray;
}
/**
* 周报导出信息
*/
@SuppressWarnings("unchecked")
public static void writeWeeklyPaper(JSONArray jsonArray){
HSSFWorkbook wb=null;
OutputStream fileOut=null;
try {
//导出模板路径地址
FileInputStream templateFileInput = new FileInputStream("E:\\excel\\weeklyPaperExportModel.xls");
POIFSFileSystem fs = new POIFSFileSystem(templateFileInput);
wb = new HSSFWorkbook(fs);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
//从第12行开始写入
int i = 11;
for(int a = 0; a < jsonArray.size(); a++){
JSONObject jsonObject = (JSONObject) jsonArray.get(a);
if ("已上线".equals(jsonObject.get("taskStatus"))) {
writeStringData(wb,""+(i-10), i, str2Num("A"),cellStyle);
writeStringData(wb,jsonObject.get("taskName").toString(), i, str2Num("B"),cellStyle);
writeStringData(wb,jsonObject.get("taskStatus").toString(), i, str2Num("F"),cellStyle);
writeStringData(wb,jsonObject.get("taskProgress").toString(), i, str2Num("G"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskStatus").toString(), i, str2Num("H"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskProgress").toString(), i, str2Num("I"),cellStyle);
writeStringData(wb,jsonObject.get("head").toString(), i, str2Num("J"),cellStyle);
i++;
}
}
for(int a = 0; a < jsonArray.size(); a++){
JSONObject jsonObject = (JSONObject) jsonArray.get(a);
if ("待上线".equals(jsonObject.get("taskStatus"))) {
writeStringData(wb,""+(i-10), i, str2Num("A"),cellStyle);
writeStringData(wb,jsonObject.get("taskName").toString(), i, str2Num("B"),cellStyle);
writeStringData(wb,jsonObject.get("taskStatus").toString(), i, str2Num("F"),cellStyle);
writeStringData(wb,jsonObject.get("taskProgress").toString(), i, str2Num("G"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskStatus").toString(), i, str2Num("H"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskProgress").toString(), i, str2Num("I"),cellStyle);
writeStringData(wb,jsonObject.get("head").toString(), i, str2Num("J"),cellStyle);
i++;
}
}
for(int a = 0; a < jsonArray.size(); a++){
JSONObject jsonObject = (JSONObject) jsonArray.get(a);
if ("业务复核".equals(jsonObject.get("taskStatus"))) {
writeStringData(wb,""+(i-10), i, str2Num("A"),cellStyle);
writeStringData(wb,jsonObject.get("taskName").toString(), i, str2Num("B"),cellStyle);
writeStringData(wb,jsonObject.get("taskStatus").toString(), i, str2Num("F"),cellStyle);
writeStringData(wb,jsonObject.get("taskProgress").toString(), i, str2Num("G"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskStatus").toString(), i, str2Num("H"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskProgress").toString(), i, str2Num("I"),cellStyle);
writeStringData(wb,jsonObject.get("head").toString(), i, str2Num("J"),cellStyle);
i++;
}
}
for(int a = 0; a < jsonArray.size(); a++){
JSONObject jsonObject = (JSONObject) jsonArray.get(a);
if ("UAT测试".equals(jsonObject.get("taskStatus"))) {
writeStringData(wb,""+(i-10), i, str2Num("A"),cellStyle);
writeStringData(wb,jsonObject.get("taskName").toString(), i, str2Num("B"),cellStyle);
writeStringData(wb,jsonObject.get("taskStatus").toString(), i, str2Num("F"),cellStyle);
writeStringData(wb,jsonObject.get("taskProgress").toString(), i, str2Num("G"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskStatus").toString(), i, str2Num("H"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskProgress").toString(), i, str2Num("I"),cellStyle);
writeStringData(wb,jsonObject.get("head").toString(), i, str2Num("J"),cellStyle);
i++;
}
}
for(int a = 0; a < jsonArray.size(); a++){
JSONObject jsonObject = (JSONObject) jsonArray.get(a);
if ("联调".equals(jsonObject.get("taskStatus"))) {
writeStringData(wb,""+(i-10), i, str2Num("A"),cellStyle);
writeStringData(wb,jsonObject.get("taskName").toString(), i, str2Num("B"),cellStyle);
writeStringData(wb,jsonObject.get("taskStatus").toString(), i, str2Num("F"),cellStyle);
writeStringData(wb,jsonObject.get("taskProgress").toString(), i, str2Num("G"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskStatus").toString(), i, str2Num("H"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskProgress").toString(), i, str2Num("I"),cellStyle);
writeStringData(wb,jsonObject.get("head").toString(), i, str2Num("J"),cellStyle);
i++;
}
}
for(int a = 0; a < jsonArray.size(); a++){
JSONObject jsonObject = (JSONObject) jsonArray.get(a);
if ("需求确认".equals(jsonObject.get("taskStatus"))) {
writeStringData(wb,""+(i-10), i, str2Num("A"),cellStyle);
writeStringData(wb,jsonObject.get("taskName").toString(), i, str2Num("B"),cellStyle);
writeStringData(wb,jsonObject.get("taskStatus").toString(), i, str2Num("F"),cellStyle);
writeStringData(wb,jsonObject.get("taskProgress").toString(), i, str2Num("G"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskStatus").toString(), i, str2Num("H"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskProgress").toString(), i, str2Num("I"),cellStyle);
writeStringData(wb,jsonObject.get("head").toString(), i, str2Num("J"),cellStyle);
i++;
}
}
for(int a = 0; a < jsonArray.size(); a++){
JSONObject jsonObject = (JSONObject) jsonArray.get(a);
if ("需求分析".equals(jsonObject.get("taskStatus"))) {
writeStringData(wb,""+(i-10), i, str2Num("A"),cellStyle);
writeStringData(wb,jsonObject.get("taskName").toString(), i, str2Num("B"),cellStyle);
writeStringData(wb,jsonObject.get("taskStatus").toString(), i, str2Num("F"),cellStyle);
writeStringData(wb,jsonObject.get("taskProgress").toString(), i, str2Num("G"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskStatus").toString(), i, str2Num("H"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskProgress").toString(), i, str2Num("I"),cellStyle);
writeStringData(wb,jsonObject.get("head").toString(), i, str2Num("J"),cellStyle);
i++;
}
}
for(int a = 0; a < jsonArray.size(); a++){
JSONObject jsonObject = (JSONObject) jsonArray.get(a);
if (!"需求分析".equals(jsonObject.get("taskStatus"))&&!"需求确认".equals(jsonObject.get("taskStatus"))&&
!"开发".equals(jsonObject.get("taskStatus"))&&!"内部测试".equals(jsonObject.get("taskStatus"))&&
!"联调".equals(jsonObject.get("taskStatus"))&&!"UAT测试".equals(jsonObject.get("taskStatus"))&&
!"业务复核".equals(jsonObject.get("taskStatus"))&&!"待上线".equals(jsonObject.get("taskStatus"))&&
!"已上线".equals(jsonObject.get("taskStatus"))) {
writeStringData(wb,""+(i-10), i, str2Num("A"),cellStyle);
writeStringData(wb,jsonObject.get("taskName").toString(), i, str2Num("B"),cellStyle);
writeStringData(wb,jsonObject.get("taskStatus").toString(), i, str2Num("F"),cellStyle);
writeStringData(wb,jsonObject.get("taskProgress").toString(), i, str2Num("G"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskStatus").toString(), i, str2Num("H"),cellStyle);
writeStringData(wb,jsonObject.get("lastTaskProgress").toString(), i, str2Num("I"),cellStyle);
writeStringData(wb,jsonObject.get("head").toString(), i, str2Num("J"),cellStyle);
i++;
}
}
//将开发和内部测试的任务写到模板（下周任务计划从103行开始写入,行数根据模板确定）
int j = 102;
for(int a = 0; a < jsonArray.size(); a++){
JSONObject jsonObject = (JSONObject) jsonArray.get(a);
if ("开发".equals(jsonObject.get("taskStatus"))) {
writeStringData(wb,""+(j-101), j, str2Num("A"),cellStyle);
writeStringData(wb,jsonObject.get("taskName").toString(), j, str2Num("B"),cellStyle);
writeStringData(wb,jsonObject.get("taskStatus").toString(), j, str2Num("F"),cellStyle);
writeStringData(wb,jsonObject.get("taskProgress").toString(), j, str2Num("H"),cellStyle);
j++;
}
}
for(int a = 0; a < jsonArray.size(); a++){
JSONObject jsonObject = (JSONObject) jsonArray.get(a);
if ("内部测试".equals(jsonObject.get("taskStatus"))) {
writeStringData(wb,""+(j-101), j, str2Num("A"),cellStyle);
writeStringData(wb,jsonObject.get("taskName").toString(), j, str2Num("B"),cellStyle);
writeStringData(wb,jsonObject.get("taskStatus").toString(), j, str2Num("F"),cellStyle);
writeStringData(wb,jsonObject.get("taskProgress").toString(), j, str2Num("H"),cellStyle);
j++;
}
}
Date date = new Date();
//将新的excel写入地址
File file=new File("E:\\excel");
OutputStream stream=null;
stream = new FileOutputStream(new File(file, new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".xls"));
wb.write(stream);
} catch (FileNotFoundException e) {
System.out.println("周报导出excel异常");
} catch (IOException e) {
System.out.println("周报导出excel异常");
}finally {
if(wb!=null){
wb = null;
}
if(fileOut!=null){
try {
fileOut.close();
} catch (IOException e) {
System.out.println("周报导出excel异常");
}
}
}
};
/**
* 向单元格写入非图片格式信息
* @描述：这是一个实体类，提供了相应的接口，用于操作Excel，在任意坐标处写入数据。
* @参数：String newContent：你要输入的内容
* int beginRow ：行坐标，Excel从 0 算起
* int beginCol ：列坐标，Excel从 0 算起
* style: 单元格样式
*/
public static void writeStringData(HSSFWorkbook wb, String newContent, int beginRow, int beginCell,HSSFCellStyle style)
{
HSSFSheet sheet=wb.getSheet("项目周报");
if(wb.getSheet("项目周报")==null){
sheet = wb.createSheet("项目周报");
}
HSSFRow row = sheet.getRow(beginRow);
if(null == row ){
//如果不做空判断，你必须让你的模板文件画好边框，beginRow和beginCell必须在边框最大值以内
//否则会出现空指针异常
row = sheet.createRow(beginRow);
}
HSSFCell cell = row.getCell(beginCell);
if(null == cell){
cell = row.createCell(beginCell);
}
if(style!=null){
cell.setCellStyle(style);
}
//向单元格中放入值
cell.setCellValue(newContent);
}
/**
* 将excel列中的A、B、AA、AB等内容转换为0、1等数字
* @param s
* @return
*/
private static int str2Num(String s) {
int r = 0;
for (int i = 0; i < s.length(); i++) {
r = r * 26 + s.charAt(i) - 'A' + 1;
}
return r - 1;
}
}
