java 功能点
1. 根据页面中table返回excel,不请求后台
.factory('exportExcelService',function($window){ var uri='data:application/vnd.ms-excel;base64,', template='<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>', base64=function(s){return $window.btoa(unescape(encodeURIComponent(s)));}, format=function(s,c){return s.replace(/{(\w+)}/g,function(m,p){ return c[p]; })}; return { tableToExcel:function(tableId,worksheetName){ var table=$(tableId); var ctx={worksheet:worksheetName,table:table.html()}; var href=uri+base64(format(template,ctx)); return href; } }; })
2. 后台请求生成excel
@RequestMapping("/exportExcelRecord")
public void ExportExcelRecord(MedicalCardModel medicalCardModel,HttpServletResponse response){
List<MedicalCardResultModel> result=medicalCardService.getMedicalRecord(medicalCardModel);
int size = result.size();
OutputStream out =null;
try {
out = ExcelBasePoiUtils.setResponse(response, "record.xlsx");
SXSSFWorkbook wb = new SXSSFWorkbook(5000);
Sheet sh = wb.createSheet();
ExcelDataFormatter edf = new ExcelDataFormatter();
Map<String, String> mapGender = new HashMap<String, String>();
mapGender.put(IS_MAN+"", GenderEnums.getName(IS_MAN));
mapGender.put(IS_WOMAN+"", GenderEnums.getName(IS_WOMAN));
edf.set("gender", mapGender);
List<MedicalCardResultModel> list =null;
for (int i = 0; i < size; i = i + EXCEL_MAX_FROM＿DB) {
list=(size-i>EXCEL_MAX_FROM＿DB?result.subList(i, i + EXCEL_MAX_FROM＿DB):result.subList(i,i+size));
ExcelBasePoiUtils.getWorkBook(list, sh, wb, edf);
}
wb.write(out);
wb.dispose();
ExcelBasePoiUtils.close(out);
} catch (Exception e) {
}
}
public static <T> void getWorkBook(List<T> list, Sheet sh, Workbook book, ExcelDataFormatter edf) throws Exception { Field[] fields = ReflectUtils.getClassFieldsAndSuperClassFields(list.get(0).getClass()); Excel excel = null; Cell cell = null; Row row = null; int columnIndex = 0; if(sh.getLastRowNum() == 0){ row = sh.createRow(0); XSSFCellStyle titleStyle = setTitleStyle(book); for (Field field : fields) { field.setAccessible(true); excel = field.getAnnotation(Excel.class); if (excel == null || excel.skip() == true) { continue; } sh.setColumnWidth(columnIndex, excel.width() * 256); cell = row.createCell(columnIndex); cell.setCellStyle(titleStyle); cell.setCellValue(excel.value()); columnIndex++; } } CellStyle cs = book.createCellStyle(); CreationHelper createHelper = book.getCreationHelper(); for (T t : list) { int rowColumn = ThreadLocalUtils.get(); row = sh.createRow(rowColumn); columnIndex = 0; Object o; for (Field field : fields) { field.setAccessible(true); excel = field.getAnnotation(Excel.class); if (excel == null || excel.skip() == true) { continue; } cell = row.createCell(columnIndex); o = field.get(t); //常用类型置前 if (o == null) { cell.setCellValue(""); }else if(o instanceof String){ cell.setCellValue((String)o); }else if (o instanceof Integer){ Integer intValue = (Integer)o; if (edf == null) { cell.setCellValue(intValue); } else { Map<String, String> map = edf.get(field.getName()); if (map == null) { cell.setCellValue(intValue); } else { cell.setCellValue(map.get(intValue.toString())); } } }else if (o instanceof Date) { cs.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); cell.setCellStyle(cs); cell.setCellValue((Date) o); } else if (o instanceof Double || o instanceof Float) { cell.setCellValue((Double) o); } /*else if (o instanceof Boolean) { Boolean boolValue = (Boolean) o; if (edf == null) { cell.setCellValue(boolValue); } else { Map<String, String> map = edf.get(field.getName()); if (map == null) { cell.setCellValue(boolValue); } else { cell.setCellValue(map.get(boolValue.toString().toLowerCase())); } } }*/else if (o instanceof Long){ Long longValue = (Long)o; cell.setCellValue(longValue); }else { cell.setCellValue(o.toString()); } columnIndex++; } ThreadLocalUtils.add(); } }
/** * 释放资源 * @param os * @throws IOException */ public static void close(OutputStream os) throws IOException{ ThreadLocalUtils.remove(); os.flush(); os.close(); }
/** * * <p>Description: Excel 注解， 配合反射完成Excel 到 javaBean 的映射</p> * <p>Company: GDKJ</p> * @author wbw * @version 1.0.0 * @date 2017年3月2日 */ @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.FIELD, ElementType.TYPE}) public @interface Excel { String value() default ""; int width() default 20; boolean skip() default false; }
@Excel(value="序号",skip=true) private Long id; @Excel("病案号") private String caseNo;
3. 导入excel
@RequestMapping("/importExcelRecord")
public DataModel<Object> ImportExcelRecord(MultipartFile file){
List<String> caseNoList = new ArrayList<String>();
try {
caseNoList = ExcelImport.readExcel(file);
if(caseNoList.size()<1)
{
return ResultMapUtils.getResultMap(model);
}
} catch (Exception e) {
e.printStackTrace();
return ResultMapUtils.getFailResultMap(Constants.GET_ERROR_KEY, e.getMessage());
}
/**
* 读excel
* @param file
* @return
* @throws IOException
*/
public static List<String> readExcel(MultipartFile file) throws IOException {
String fileName = file.getOriginalFilename();
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
.substring(fileName.lastIndexOf(".") + 1);
if ("xls".equals(extension)) {
return read2003Excel(file);
} else if ("xlsx".equals(extension)) {
return read2007Excel(file);
} else {
throw new IOException("不支持的文件类型");
}
}
/**
* 读取 office 2003 excel
* 第二个参数： 是哪列开启非空效验，如果是-1就开启全部非空效验,列从0开始
* @throws IOException
* @throws FileNotFoundException
* @author wbw
*/
private static List<String> read2003Excel(MultipartFile file)
throws IOException {
HSSFWorkbook hwb = new HSSFWorkbook(file.getInputStream());
HSSFSheet sheet = hwb.getSheetAt(0);
String value = null;
HSSFRow row = null;
HSSFCell cell = null;
List<String> result = new ArrayList<String>();
for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++)
{
row = sheet.getRow(i);
if (row == null) {
continue;
}
int firstColumn=row.getFirstCellNum();
cell = row.getCell(firstColumn);
if (cell == null)
{
continue;
}
DecimalFormat df = new DecimalFormat("0");// 格式化 number String
switch (cell.getCellType())
{
case XSSFCell.CELL_TYPE_STRING:
value =cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString()))
{
value = df.format(cell.getNumericCellValue()).toString();
} else if ("General".equals(cell.getCellStyle()
.getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
}
break;
default:
value = "";
}
if (value == null || "".equals(value)) {
continue;
}
result.add(value);
}
return result;
}
/**
* 读取Office 2007 excel
* */
private static List<String> read2007Excel(MultipartFile file)
throws IOException {
XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream());
XSSFSheet sheet = xwb.getSheetAt(0);
String value = null;
XSSFRow row = null;
XSSFCell cell = null;
List<String> result = new ArrayList<String>();
for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++)
{
row = sheet.getRow(i);
if (row == null) {
continue;
}
cell = row.getCell(row.getFirstCellNum());
if (cell == null) {
continue;
}
DecimalFormat df = new DecimalFormat("0");// 格式化 number String
switch (cell.getCellType())
{
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString()))
{
value = df.format(cell.getNumericCellValue()).toString();
} else if ("General".equals(cell.getCellStyle()
.getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
}
break;
default:
value = "";
}
if (value == null || "".equals(value)) {
continue;
}
result.add(value);
}
return result;
}
4. 点击编辑，编辑变保存，输入框可编辑，获取焦点
<td style="padding: 0px 0px;"> <input type="text" name="correction" class="form-control" ng-model="cor.correction" ng-readonly="checkEdit"/> </td> <td>{{cor.operator}}</td> <td>{{cor.updatetime | date:'yyyy-MM-dd'}}</td> <td> <a href="javascript:void(0)" class="operate_a" ng-click="editSec($event,cor.id,cor.correction)">编辑</a>
$scope.checkEdit = true;
$scope.editSec = function (event,id,context) { if(angular.element(event.target).context.innerHTML == "编辑"){ $scope.checkEdit = false; angular.element(event.target).parents("tr").find("input").focus(); angular.element(event.target).context.innerHTML = "保存"; }else{ $scope.updateCor(id,context); $scope.checkEdit = true; angular.element(event.target).context.innerHTML = "编辑"; } }
