前台参照之前js数组转换成json数组的博文
后台:
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
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;
/**
* 导出日报的Excel文件
*/
public String exportRptDailyExcel(TProjProject currProj,String writeStartDate,String writeEndDate, List<RptDailyData> exportDataList) {
String result = "";
// 打开Excel模板
excelRptDailyTemplateAbsolutePath = calFileAbsoPathInTomcat(excelRptDailyTemplateRelativePath);
FileInputStream is = null;
try {
is = new FileInputStream(excelRptDailyTemplateAbsolutePath);
} catch (FileNotFoundException e2) {
e2.printStackTrace();
}
HSSFWorkbook wbs = null;
try {
wbs = new HSSFWorkbook(is);
} catch (IOException e1) {
e1.printStackTrace();
}
// 读取测点数据Sheet
HSSFSheet rptDailyDataSheet = wbs.getSheetAt(0);
System.out.println("行数: " + (rptDailyDataSheet.getLastRowNum() + 1));
// 写入表格title,表格title不用写,模板里面有
// HSSFRow gridTitleRow = pointDataSheet.createRow(2);
// HSSFCell gridTitleCell = gridTitleRow.createCell(0);
// gridTitleCell.setCellValue("测点名称");
//
// gridTitleCell = gridTitleRow.createCell(1);
// gridTitleCell.setCellValue("监测时间");
//
// gridTitleCell = gridTitleRow.createCell(2);
// gridTitleCell.setCellValue("期次");
//
// gridTitleCell = gridTitleRow.createCell(3);
// gridTitleCell.setCellValue("初始测值");
//
// gridTitleCell = gridTitleRow.createCell(4);
// gridTitleCell.setCellValue("本次测值");
//
// gridTitleCell = gridTitleRow.createCell(5);
// gridTitleCell.setCellValue("上次测值");
//
// gridTitleCell = gridTitleRow.createCell(6);
// gridTitleCell.setCellValue("单次变化");
//
// gridTitleCell = gridTitleRow.createCell(7);
// gridTitleCell.setCellValue("累计变化");
//
// gridTitleCell = gridTitleRow.createCell(8);
// gridTitleCell.setCellValue("变化速率");
// 填充数据
HSSFRow dataRow;
for (int i = 0; i < exportDataList.size(); i++) {
dataRow = rptDailyDataSheet.createRow(i + 3); // 注意! 这里要加2,是因为表格中前面已经占据了三行,分别提示信息、显示总标题和表格标题
RptDailyData rptDaily = exportDataList.get(i);
System.out.print(rptDaily.getChangeRate());
// 创建单元格,并设置值
if(rptDaily.getWriteDate()==null){
dataRow.createCell(0).setCellValue("/");
}else{
dataRow.createCell(0).setCellValue(rptDaily.getWriteDate());
}
if(rptDaily.getpStatusReport()==null){
dataRow.createCell(1).setCellValue("/");
}else{
dataRow.createCell(1).setCellValue(rptDaily.getpStatusReport());
}
if(rptDaily.getPName()==null){
dataRow.createCell(2).setCellValue("/");
}else{
dataRow.createCell(2).setCellValue(rptDaily.getPName());
}
if(rptDaily.getMonitorDate()==null){
dataRow.createCell(3).setCellValue("/");
}else{
dataRow.createCell(3).setCellValue(rptDaily.getMonitorDate());
}
if(rptDaily.getWeather()==null){
dataRow.createCell(4).setCellValue("/");
}else{
dataRow.createCell(4).setCellValue(rptDaily.getWeather());
}
if(rptDaily.getWritePerson()==null){
dataRow.createCell(5).setCellValue("/");
}else{
dataRow.createCell(5).setCellValue(rptDaily.getWritePerson());
}
if(rptDaily.getMmName()==null){
dataRow.createCell(6).setCellValue("/");
}else{
dataRow.createCell(6).setCellValue(rptDaily.getMmName());
}
dataRow.createCell(7).setCellValue(rptDaily.getIssue());
if(rptDaily.getPointName()==null){
dataRow.createCell(8).setCellValue("/");
}else{
dataRow.createCell(8).setCellValue(rptDaily.getPointName());
}
if(rptDaily.getThisvalue()==null){
dataRow.createCell(9).setCellValue("/");
}else{
dataRow.createCell(9).setCellValue(rptDaily.getThisvalue());
}
if(rptDaily.getChangeThis()==null){
dataRow.createCell(10).setCellValue("/");
}else{
dataRow.createCell(10).setCellValue(rptDaily.getChangeThis());
}
if(rptDaily.getChangeTotal()==null){
dataRow.createCell(11).setCellValue("/");
}else{
dataRow.createCell(11).setCellValue(rptDaily.getChangeTotal());
}
if(rptDaily.getChangeRate()==null){
dataRow.createCell(12).setCellValue("/");
}else{
dataRow.createCell(12).setCellValue(rptDaily.getChangeRate());
}
}
// 将文件存到指定位置
try {
String dirPath = "ReportExcelFile/";
String folderDir = getFolderPath(dirPath);
String projNamePart = "";
String projMethodPart = "";
if (currProj.getPName().length() >= 5) {
projNamePart = currProj.getPName().substring(0, 5);
} else {
projNamePart = currProj.getPName();
}
// 计算最终的文件名
String fileName = converterToSpell(projNamePart) + "_RptDaily"+writeStartDate+"_"+writeEndDate+".xls";
// 创建文件夹
File folder = new File(folderDir);
if (!folder.exists() && !folder.isDirectory()) {
System.out.println("目录不存在,准备创建该目录");
folder.mkdirs();
}
String exportRptDailyFilePath = "";
exportRptDailyFilePath = folderDir + fileName;
System.out.println("文件夹目录:" + folderDir);
System.out.println("exportPjmiFilePath = " + exportRptDailyFilePath);
FileOutputStream fout = new FileOutputStream(exportRptDailyFilePath);
wbs.write(fout);
fout.close();
result = dirPath + fileName;
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 汉字转换位汉语拼音,英文字符不变;导出的文件名中的工程名用拼音
* @param chines 汉字
* @return 拼音
*/
private String converterToSpell(String chines) {
String pinyinName = "";
char[] nameChar = chines.toCharArray();
HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);
defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
for (int i = 0; i < nameChar.length; i++) {
if (nameChar[i] > 128) {
try {
pinyinName += PinyinHelper.toHanyuPinyinStringArray(
nameChar[i], defaultFormat)[0] + '_';
} catch (BadHanyuPinyinOutputFormatCombination e) {
e.printStackTrace();
}
} else {
pinyinName += nameChar[i];
}
}
return pinyinName.substring(0, pinyinName.length() - 1);
}
/*
* 按日报填写日期,顺序冒泡排序
*
* */
public void SortByWriteDate(List<RptDailyData> exportDataList) {
// TODO Auto-generated method stub
RptDailyData temp = null;
boolean exchange = false;
for (int i = 0; i < exportDataList.size(); i++) {
exchange = false;
for (int j = exportDataList.size() - 2; j >= i; j--) {
if (exportDataList.get(j + 1).getWriteDate().compareTo(
exportDataList.get(j).getWriteDate()) < 0) {
temp = exportDataList.get(j + 1);
exportDataList.set(j + 1, exportDataList.get(j));
exportDataList.set(j, temp);
exchange = true;
}
}
if (!exchange)
break;
}
return;
}