Java用Intellij Idea读取Execel文件,计算后写入新的文件
1、新建Maven工程成功后,pom.xml文件中配置poi(读写excel的框架)
<dependencies>
...
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!--ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
</dependencies>
2、获取excel文件路径(
D:\\ItelliJ_Idea\\WorkSplace\\smallwalnut\\src\\main\\resources\\(3)_15.xlsx
)
3、主要java文件(也可以用kotlin)
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
public class ReadWriteExcel {
public static void main(String[] argv) {
ReadWriteExcel readWriteExcel = new ReadWriteExcel();
try {
readWriteExcel.readInfo();
} catch (Exception e) {
e.printStackTrace();
}
}
public void readInfo() throws Exception {
//Excel文件
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File("D:\\ItelliJ_Idea\\WorkSplace\\smallwalnut\\src\\main\\resources\\(3)_15.xlsx")));//读取文件路径
for (int j = 0; j < wb.getNumberOfSheets(); j++) {
//取工作表
XSSFSheet sheet = wb.getSheetAt(j);
String sheetName = sheet.getSheetName();
System.out.println("sheet=" + j + ",sheetName=" + sheetName);
//循环读出每条记录,第0行为标题行
if (sheetName.equals("Sheet1")) {
DecimalFormat df = new DecimalFormat("0.00");//格式化小数,不足的补0
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
XSSFRow row = sheet.getRow(i);
XSSFCell codeCell = row.getCell(0);
if (codeCell != null) {
try {
if (i == 0) { //第一行是标题栏,可自由定制,下面表示第一行第7,8,9列的新增名称
row.createCell(7).setCellValue("new7");
row.createCell(8).setCellValue("new8");
row.createCell(9).setCellValue("new9");
continue;
}
codeCell.setCellType(XSSFCell.CELL_TYPE_STRING); //设置单元格格式为String,否则无法用字符串接收一个将数字格式的cell
String code = codeCell.getStringCellValue();
codeCell.getRow().getRowNum();
Double firstS = row.getCell(1).getNumericCellValue(); //单元格为数字类型
Double secondPercent = row.getCell(2).getNumericCellValue(); //单元格为数字类型
Double thirdDelay = row.getCell(3).getNumericCellValue(); //单元格为数字类型
caculateThirdDate(j, code, firstS, secondPercent, thirdDelay,row,df);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
//开始写
FileOutputStream fileOut = new FileOutputStream(new File("D:\\ItelliJ_Idea\\WorkSplace\\smallwalnut\\src\\main\\resources\\(3)_16.xlsx"));//输出文件路径
// 将workbook写到输出流中
wb.write(fileOut);
fileOut.flush();
fileOut.close();
wb.close();
}
private void caculateThirdDate(int j, String name, Double firstS, Double secondPercent, Double thirdDelay, XSSFRow row, DecimalFormat df) {
double firstSC = firstS * 1024;
double speedScore = 100;
if (firstS >= 6) {
speedScore = 100;
} else if (firstS >= 4 && firstS < 6) {
speedScore = (double) (100 - 90) / (6 - 4) * (firstS - 4) + 90;
} else if (firstS >= 2 && firstS < 4) {
speedScore = (double) (90 - 80) / (4 - 2) * (firstS - 2) + 80;
} else if (firstS >= 1 && firstS < 2) {
speedScore = (double) (80 - 70) / (2 - 1) * (firstS - 1) + 70;
} else if (firstSC >= 512 && firstSC < 1024) {
speedScore = (double) (70 - 60) / (1024 - 512) * (firstSC - 512) + 60;
} else if (firstSC >= 128 && firstSC < 512) {
speedScore = (double) (60 - 30) / (512 - 128) * (firstSC - 128) + 30;
} else if (firstSC < 128) {
speedScore = 0;
}
int successScore = 100;
if (secondPercent >= 0.98) {
successScore = 100;
} else if (secondPercent >= 0.96 && secondPercent < 0.98) {
successScore = (int) ((100 - 90) / (0.98 - 0.96) * (secondPercent - 0.96) + 90);
} else if (secondPercent >= 0.94 && secondPercent < 0.96) {
successScore = (int) ((90 - 80) / (0.96 - 0.94) * (secondPercent - 0.94) + 80);
} else if (secondPercent >= 0.92 && firstS < 0.94) {
successScore = (int) ((80 - 70) / (0.94 - 0.92) * (secondPercent - 0.92) + 70);
} else if (secondPercent >= 0.9 && secondPercent < 0.92) {
successScore = (int) ((70 - 60) / (0.92 - 0.9) * (secondPercent - 0.9) + 60);
} else if (secondPercent >= 0.8 && secondPercent < 0.9) {
successScore = (int) ((60 - 30) / (0.9 - 0.8) * (secondPercent - 0.8) + 30);
} else if (secondPercent < 0.8) {
successScore = 0;
}
double delayScore = 0;
if (thirdDelay <= 1000) {
delayScore = 100;
} else if (thirdDelay > 1000 && thirdDelay <= 2000) {
delayScore = ((double)(100 - 90) / (1000 - 2000) * (thirdDelay - 2000)) + 90;
} else if (thirdDelay > 2000 && thirdDelay <= 3000) {
delayScore = (double) (90 - 80) / (2000 - 3000) * (thirdDelay - 3000) + 80;
} else if (thirdDelay > 3000 && thirdDelay <= 4000) {
delayScore = (double) (80 - 70) / (3000-4000) * (thirdDelay - 4000) + 70;
} else if (thirdDelay > 4000 && thirdDelay <= 5000) {
delayScore = (double) (70 - 60) / (4000 - 5000) * (thirdDelay - 5000) + 60;
} else if (thirdDelay > 5000 && thirdDelay <= 8000) {
delayScore = (double) (60 - 30) / (5000 - 8000) * (thirdDelay - 8000) + 30;
} else{
delayScore = 0;
}
//每行7,8,9列数值设置
row.createCell(7).setCellValue(df.format(speedScore));
row.createCell(8).setCellValue(successScore);
row.createCell(9).setCellValue(df.format(delayScore));
System.out.println("name=" + name +",sheet=" + j + ",firstS = " + firstS + ",firstSC=" + firstSC
+ ",secondPercent=" + secondPercent + ",thirdDelay=" + thirdDelay
+ ",speedScore=" + speedScore + ",successScore=" + successScore
+ ",delayScore=" + delayScore
);
}
}
PS:具体文件写入方式可以自由定制