package com.system.dqfn.action;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.springframework.beans.BeanUtils;
import com.system.action.SystemAction;
import com.system.common.CommonFunction;
import com.system.common.Constants;
import com.system.db.C3P0Utils;
import com.system.dqfn.model.DQFNBom;
import com.system.dqfn.model.DQFNPlan;
import com.system.dqfn.model.DQFNPlanBom;
import com.system.dqfn.model.DQFNPlanImport;
import com.system.dqfn.model.DQFNTcline;
import com.system.dqfn.util.AjaxResult;
import com.system.dqfn.util.ExcelUtil;
import com.system.dqfn.util.PlanImportUtils;
import com.system.model.Protype;
import com.system.model.User;
import com.system.utils.UuidUtil;
@SuppressWarnings("serial")
public class DQFNPlanImportAction extends SystemAction {
private String uploadContentType;
private File file;
private String filename;
public String getFilename() {
return filename;
}
public void setFilename(String filename) {
this.filename = filename;
}
public String toExcel() {
return "";
}
public String getUploadContentType() {
return uploadContentType;
}
public void setUploadContentType(String uploadContentType) {
this.uploadContentType = uploadContentType;
}
public File getFile() {
return file;
}
public void setFile(File file) {
this.file = file;
}
@SuppressWarnings("unchecked")
public String toListUI() {
String tf_flag = res.getParameter(request, "tf_flag", "N");
String sql="select count(tf_id) total from tb_dqfn_plan_import where tf_flag='N'";
String sql_Y="select count(tf_id) total from tb_dqfn_plan_import where tf_flag='Y'";
String hql="from DQFNPlanImport where tf_flag ='"+tf_flag+"'";
//计算总数
Integer total= tf_flag.equals("Y")? C3P0Utils.getTotal(sql_Y):C3P0Utils.getTotal(sql);
request.setAttribute("total", total);
int countPage = (total / Constants.EXCELPAGESIZE) + (total % Constants.EXCELPAGESIZE > 0 ? 1 : 0);
request.setAttribute("countPage",countPage );
//判断有多少页可以浏览
int pageNo=1;
if(request.getParameter(Constants.PAGE_NO)!= null){
pageNo=Integer.parseInt(request.getParameter(Constants.PAGE_NO));//获得当前页
}
List<DQFNPlanImport> list = (List<DQFNPlanImport>) systemService.findListByHql(hql, Constants.EXCELPAGESIZE, pageNo);
request.setAttribute("pageNo", pageNo);
request.setAttribute("list", list);
request.setAttribute("tf_flag", tf_flag);
return "toListUI";
}
/**
* 导入数据
*/
public void importData() {
StringBuffer message = new StringBuffer();
ExcelUtil<DQFNPlanImport> util = new ExcelUtil<DQFNPlanImport>(DQFNPlanImport.class);
try {
InputStream in = new FileInputStream(file);
List<DQFNPlanImport> list = util.importExcel(in);
for (DQFNPlanImport t : list) {
try {
if(t.getTf_dingdan()!= null && !"".equals(t.getTf_dingdan())){
BigDecimal bigDecimal = new BigDecimal(t.getTf_dingdan().trim());//创建BigDecimal对象，把科学计数转成数字
String tf_dingdan = bigDecimal.toPlainString();
t.setTf_dingdan(tf_dingdan);
t.setTf_id(UuidUtil.get32UUID());
systemService.save(t);
}
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (Exception e1) {
e1.printStackTrace();
}
try {
response.getWriter().print(message.toString());
} catch (IOException e) {
e.printStackTrace();
}
}
//确认下单
@SuppressWarnings("unchecked")
public void toBatConfirm() {
AjaxResult result = AjaxResult.success();
List<DQFNPlanImport> list = (List<DQFNPlanImport>) systemService
.findListByHql("from DQFNPlanImport e where tf_flag = 'N'");
if (list != null && list.size() > 0) {
DQFNPlanImport planImport = null;
for (int i = 0,size = list.size();i < size;i++) {
planImport = list.get(i);
String hql = PlanImportUtils.getHqlBy(planImport);
if(!hql.equals("")){
Protype protype = (Protype) systemService
.findObjectByHql("from Protype p where p.tf_name = '"+ PlanImportUtils.dealString(planImport.getTf_product_model()) +"'");
if(protype != null){
hql += " and tf_protype = '"+ protype.getTf_id() +"'";
List<DQFNBom> listBom = (List<DQFNBom>) systemService.findListByHql(hql);
if(listBom != null && listBom.size() > 0){//可以有
if(listBom.size() == 1){//直接下单
result = toPlan(listBom.get(0), planImport);
if(Integer.parseInt(result.get("code")+"") == 0){
planImport.setTf_flag("Y");
systemService.update(planImport);
}
}
}
}
}
}
}
try {
response.sendRedirect("dqfnPlanimportAction!toListUI.action?page.page=1&tf_flag=N");
} catch (IOException e) {
e.printStackTrace();
}
}
//确认下单
@SuppressWarnings("unchecked")
public String toConfirm(){
AjaxResult result = AjaxResult.success();
String tf_id=res.getParameter(request, "tf_id", "");
DQFNPlanImport planImport= (DQFNPlanImport) systemService
.findObjectByHql("from DQFNPlanImport e where e.tf_id = '" + tf_id + "'");
System.out.println(planImport.toString());
if(planImport != null){
/**
* 下单操作
* 1.产品型号&晶圆型号 定位BOM信息
*/
String hql = PlanImportUtils.getHqlBy(planImport);
if(!hql.equals("")){
Protype protype = (Protype) systemService
.findObjectByHql("from Protype p where p.tf_name = '"+ PlanImportUtils.dealString(planImport.getTf_product_model()) +"'");
if(protype != null){
hql += " and tf_protype = '"+ protype.getTf_id() +"'";
List<DQFNBom> listBom = (List<DQFNBom>) systemService.findListByHql(hql);
if(listBom != null && listBom.size() > 0){//可以有
if(listBom.size() == 1){//直接下单
result = toPlan(listBom.get(0), planImport);
if(Integer.parseInt(result.get("code")+"") == 0){
planImport.setTf_flag("Y");
systemService.update(planImport);
}
}else{//可匹配多个BOM信息,需要转到人工确认界面进行下单
request.setAttribute("listBom", listBom);
request.setAttribute("planImport", planImport);
return "ackPlanUI";
}
}else{
log.error("无匹配的BOM信息-->"+hql);
result = AjaxResult.error("无匹配的BOM信息"+hql);
}
}else{
result = AjaxResult.error("产品型号不存在");
log.error("产品型号不存在");
}
}else{
result = AjaxResult.error(500, "产品型号不能为空");
log.error("产品型号不能为空");
}
}
try {
if(Integer.parseInt(result.get("code")+"") > 0){
request.setAttribute("errMsg", result.get("msg"));
return "errMsg";
}else{
response.sendRedirect("dqfnPlanimportAction!toListUI.action?page.page=1&tf_flag=N");
}
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 前端请求下单
* @return
*/
public String toPlan(){
String import_id = res.getParameter(request,"import_id", "");
String bom_id = res.getParameter(request, "bom_id", "");
DQFNPlanImport planImport = (DQFNPlanImport) systemService.findObjectByHql("From DQFNPlanImport where tf_id = '"+ import_id +"'");
DQFNBom bom = (DQFNBom) systemService.findObjectByHql("from DQFNBom where tf_id = '"+ bom_id +"'");
AjaxResult result = AjaxResult.success();
if(planImport != null && bom != null){
result = toPlan(bom, planImport);
if(Integer.parseInt(result.get("code")+"") == 0){
planImport.setTf_flag("Y");
systemService.update(planImport);
}
}
try {
if(Integer.parseInt(result.get("code")+"") > 0){
request.setAttribute("errMsg", result.get("msg"));
return "errMsg";
}else{
response.sendRedirect("dqfnPlanimportAction!toListUI.action?page.page=1&tf_flag=N");
}
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 执行下单任务
* @param bom
* @param planImport
* @return
*/
public AjaxResult toPlan(DQFNBom bom,DQFNPlanImport planImport) {
AjaxResult result = AjaxResult.success();
DQFNPlan plan = new DQFNPlan();
// 检测订单是否存在...
String hql = "from DQFNPlan p where p.tf_dingdan = '" + planImport.getTf_dingdan() + "'";
int f = dqfnPlanService.getTotal(hql);
if (f == 0) {
//检测BOM中的工艺路线是否存在
DQFNTcline tcline = (DQFNTcline) systemService.findObjectByHql("from DQFNTcline where tf_id = '"+ bom.getTf_tcline() +"'");
if(tcline != null){
plan.setTf_id(UuidUtil.get32UUID());
plan.setTf_bom(bom.getTf_id());
plan.setTf_wlbm("");
plan.setTf_chTime(planImport.getTf_delivery_time());
plan.setTf_color(planImport.getTf_color());
plan.setTf_dingdan(planImport.getTf_dingdan());
plan.setTf_yongtu(planImport.getTf_type());
plan.setTf_tcline(bom.getTf_tcline());
plan.setTf_taskno(planImport.getTf_taskno());
// 确定开始与结束批号
plan.setTf_jyaph(planImport.getTf_wafer_a_ph());
plan.setTf_jyatr(planImport.getTf_number());
plan.setTf_jyaps(planImport.getTf_wafer_a_ps());
plan.setTf_jyabz(planImport.getTf_wafer_a_bz());
plan.setTf_jybph(planImport.getTf_wafer_b_ph());
plan.setTf_jybps(planImport.getTf_wafer_b_ps());
plan.setTf_jybbz(planImport.getTf_wafer_b_bz());
plan.setTf_jycph(planImport.getTf_wafer_b_ph());
plan.setTf_jycps(planImport.getTf_wafer_c_ps());
plan.setTf_jycbz(planImport.getTf_wafer_c_bz());
plan.setTf_jydph(planImport.getTf_wafer_d_ph());
plan.setTf_jydps(planImport.getTf_wafer_d_ps());
plan.setTf_jydbz(planImport.getTf_wafer_d_bz());
plan.setTf_mark_a(planImport.getTf_mark_a().trim());
plan.setTf_mark_b(planImport.getTf_mark_b().trim());
plan.setTf_mark_c(planImport.getTf_mark_c().trim());
plan.setTf_mark_d(planImport.getTf_mark_d().trim());
plan.setTf_p1(planImport.getTf_p1());
plan.setTf_p2(planImport.getTf_p2());
plan.setTf_p3(planImport.getTf_p3());
plan.setTf_remarks(planImport.getTf_remarks());
List<String> lot = getLot(plan);
plan.setTf_slot(lot.get(0));
plan.setTf_elot(lot.get(1));
plan.setTf_gyFlag("N");
plan.setTf_qcFlag("N");
plan.setTf_makeFlag("N");
plan.setTf_overFlag("N");
plan.setTf_manager("N");
plan.setTf_inputdate(sdf.format(new Date()));
plan.setTf_inputmen(((User) request.getSession().getAttribute("login")).getTf_name());
System.out.println(plan.toString());
dqfnPlanService.save(plan);
try {
DQFNPlanBom planBom = new DQFNPlanBom();
BeanUtils.copyProperties(bom, planBom);
planBom.setTf_id(UuidUtil.get32UUID());
planBom.setTf_dingdan(plan.getTf_dingdan());
systemService.save(planBom);
plan.setTf_bom(planBom.getTf_id());
systemService.update(plan);
} catch (Exception e) {
result = AjaxResult.error(e.getMessage());
dqfnPlanService.delete(plan);
}
}else{
result = AjaxResult.error("BOM的工艺路线未维护，请确认...");
}
} else {
result = AjaxResult.error("此订单号已存在,请检查后再提交...");
}
return result;
}
// 获取批号
@SuppressWarnings("unchecked")
public List<String> getLot(DQFNPlan plan) {
List<String> result = new ArrayList<String>();
// 开始与结束批号确定
String hql = "select model.tf_elot from DQFNPlan model where substring(model.tf_elot,3,4) = '"
+ CommonFunction.getYearWeek() + "' order by substring(model.tf_elot,7,2) desc";
// System.out.println(hql);
List<String> list = (List<String>) dqfnPlanService.findByString(hql);
DQFNBom dqfnBom = dqfnBomService.findById("from DQFNBom b where b.tf_id = '" + plan.getTf_bom() + "'");
// 每LOT片数
// 计算此处。。。。。
String Lot = "";
if ("样品".equals(plan.getTf_yongtu()) || "试产".equals(plan.getTf_yongtu())) {
Lot = "RE" + CommonFunction.getYearWeek();
} else {
Lot = "RP" + CommonFunction.getYearWeek();
}
int plot = Integer.parseInt(plan.getTf_jyatr());
int lot = Integer.parseInt(dqfnBom.getTf_fmpcs()) * Integer.parseInt(dqfnBom.getTf_lotfm());
int lots = plot / lot + ((plot % lot) > 0 ? 1 : 0);
if (list != null) {
Lot = Lot + CommonFunction.getRlot(list.get(0).substring(6, 8));
} else {
Lot = Lot + CommonFunction.getRlot("");
}
String lotss = "000" + lots;
lotss = lotss.substring(lotss.length() - 3, lotss.length());
String tf_slot = Lot + "001";
String tf_elot = Lot + lotss;
result.add(tf_slot);
result.add(tf_elot);
return result;
}
//删除
public void toDel() {
String tf_id=res.getParameter(request, "tf_id", "");
DQFNPlanImport del = (DQFNPlanImport) systemService
.findObjectByHql("from DQFNPlanImport e where e.tf_id = '" + tf_id + "' and tf_flag = 'N'");
if(del != null)
systemService.delete(del);
try {
response.sendRedirect("dqfnPlanimportAction!toListUI.action");
} catch (IOException e) {
e.printStackTrace();
}
}
// 清除未下单
public void toRemove() {
@SuppressWarnings("unchecked")
List<DQFNPlanImport> list = (List<DQFNPlanImport>) systemService
.findListByHql("from DQFNPlanImport where tf_flag = 'N'");
for (DQFNPlanImport i : list) {
systemService.delete(i);
}
try {
response.sendRedirect("dqfnPlanimportAction!toListUI.action");
} catch (IOException e) {
e.printStackTrace();
}
}
//toBack
public void toBack(){
String tf_id=res.getParameter(request, "tf_id", "");
DQFNPlanImport planImport = (DQFNPlanImport) systemService
.findObjectByHql("from DQFNPlanImport where tf_id = '" + tf_id + "'");
if(planImport != null){
planImport.setTf_flag("N");
systemService.update(planImport);
}
try {
response.sendRedirect("dqfnPlanimportAction!toListUI.action");
} catch (IOException e) {
e.printStackTrace();
}
}
}
package com.system.dqfn.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.system.dqfn.model.Excel;
import com.system.dqfn.model.Excel.Type;
import com.system.utils.BusinessException;
/**
* Excel相关处理
* @author xiejia
*/
public class ExcelUtil<T> {
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
/**
* Excel sheet最大行数，默认65536
*/
public static final int sheetSize = 65536;
/**
* 工作表名称
*/
private String sheetName;
/**
* 导出类型（EXPORT:导出数据；IMPORT：导入模板）
*/
private Excel.Type type;
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 导入导出数据列表
*/
private List<T> list;
/**
* 注解列表
*/
private List<Field> fields;
/**
* 实体对象
*/
public Class<T> clazz;
public ExcelUtil(Class<T> clazz)
{
this.clazz = clazz;
}
public void init(List<T> list, String sheetName, Excel.Type type)
{
if (list == null)
{
list = new ArrayList<T>();
}
this.list = list;
this.sheetName = sheetName;
this.type = type;
createExcelField();
createWorkbook();
}
/**
* 对list数据源将其里面的数据导入到excel表单
*
* @param sheetName 工作表的名称
* @return 结果
*/
public AjaxResult importTemplateExcel(String sheetName){
this.init(null, sheetName, Excel.Type.IMPORT);
return exportExcel();
}
/**
* 对excel表单默认第一索引名转换成list
* @param is 输入流
* @return 转换后集合
*/
public List<T> importExcel(InputStream is) throws Exception{
return importExcel(StringUtils.EMPTY,is);
}
public List<T> importExcel(String sheetName,InputStream is) throws Exception{
this.type = Type.IMPORT;
this.wb = WorkbookFactory.create(is);
List<T> list = new ArrayList<T>();
Sheet sheet = null;
if (StringUtils.isNotEmpty(sheetName)){
// 如果指定sheet名,则取指定sheet中的内容.
sheet = wb.getSheet(sheetName);
}else{
// 如果传入的sheet名不存在则默认指向第1个sheet.
sheet = wb.getSheetAt(0);
}
if (sheet == null){
throw new IOException("文件sheet不存在");
}
int rows = sheet.getPhysicalNumberOfRows();
if (rows > 0){
// 默认序号
int serialNum = 0;
// 有数据时才处理 得到类的所有field.
Field[] allFields = clazz.getDeclaredFields();
// 定义一个map用于存放列的序号和field.
Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
for (int col = 0; col < allFields.length; col++)
{
Field field = allFields[col];
Excel attr = field.getAnnotation(Excel.class);
if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
{
// 设置类的私有字段属性可访问.
field.setAccessible(true);
fieldsMap.put(++serialNum, field);
}
}
for (int i = 2; i < rows; i++){
// 从第3行开始取数据,默认第一行是表头.
Row row = sheet.getRow(i);
int cellNum = serialNum;
T entity = null;
for (int column = 0; column < cellNum; column++){
Object val = this.getCellValue(row, column);
// 如果不存在实例则新建.
entity = (entity == null ? clazz.newInstance() : entity);
// 从map中得到对应列的field.
Field field = fieldsMap.get(column + 1);
// 取得类型,并根据对象类型设置值.
Class<?> fieldType = field.getType();
System.out.println(fieldType+"*****"+val);
if (String.class == fieldType){
try{
BigDecimal bigDecimal = new BigDecimal(val+"");
val = bigDecimal.toPlainString();
String s = Convert.toStr(val);
if (StringUtils.endsWith(s, ".0")){
try {
val = StringUtils.substringBefore(s, ".0");
int f = Integer.parseInt(val+"");
}catch(Exception e) {
val = s;
}
//产品型号为.0结束...
}else{
val = Convert.toStr(val);
}
}catch(Exception e){}
}else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)){
val = Convert.toInt(val);
}else if ((Long.TYPE == fieldType) || (Long.class == fieldType)){
val = Convert.toLong(val);
}else if ((Double.TYPE == fieldType) || (Double.class == fieldType)){
val = Convert.toDouble(val);
}else if ((Float.TYPE == fieldType) || (Float.class == fieldType)){
val = Convert.toFloat(val);
}else if (BigDecimal.class == fieldType){
val = Convert.toBigDecimal(val);
}else if (Date.class == fieldType){
if (val instanceof String){
val = DateUtils.parseDate(val);
}else if (val instanceof Double){
val = DateUtil.getJavaDate((Double) val);
}
}
if (StringUtils.isNotNull(fieldType)){
Excel attr = field.getAnnotation(Excel.class);
String propertyName = field.getName();
if (StringUtils.isNotEmpty(attr.targetAttr())){
propertyName = field.getName() + "." + attr.targetAttr();
}else if (StringUtils.isNotEmpty(attr.readConverterExp())){
val = reverseByExp(String.valueOf(val), attr.readConverterExp());
}
ReflectUtils.invokeSetter(entity, propertyName, val);
}
}
list.add(entity);
}
}
return list;
}
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
@SuppressWarnings("deprecation")
public Object getCellValue(Row row, int column)
{
if (row == null){
return row;
}
Object val = "";
try{
Cell cell = row.getCell(column);
if (cell != null){
// System.out.println(cell.getCellTypeEnum()+"---->>单元格样式");
if (cell.getCellTypeEnum() == CellType.NUMERIC){
val = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell)){
val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
}else {
//System.out.println(val+"--->>>>>");
}
/*else{
if ((Double) val % 1 > 0){
val = new DecimalFormat("0.00").format(val);
}else{
val = new DecimalFormat("0").format(val);
}
}*/
}else if (cell.getCellTypeEnum() == CellType.STRING){
val = cell.getStringCellValue();
}else if (cell.getCellTypeEnum() == CellType.BOOLEAN){
val = cell.getBooleanCellValue();
}else if (cell.getCellTypeEnum() == CellType.ERROR){
val = cell.getErrorCellValue();
}
}
}catch (Exception e){
e.printStackTrace();
return val;
}
return val;
}
/**
* 对list数据源将其里面的数据导入到excel表单
*
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @return 结果
*/
public AjaxResult exportExcel(List<T> list, String sheetName)
{
this.init(list, sheetName, Excel.Type.EXPORT);
return exportExcel();
}
/**
* 对list数据源将其里面的数据导入到excel表单
*
* @return 结果
*/
public AjaxResult exportExcel()
{
OutputStream out = null;
try
{
// 取出一共有多少个sheet.
double sheetNo = Math.ceil(list.size() / sheetSize);
for (int index = 0; index <= sheetNo; index++)
{
createSheet(sheetNo, index);
Cell cell = null; // 产生单元格
Cell cell2 = null; // 产生单元格
// 产生一行
Row row = sheet.createRow(0);
Row row2 = sheet.createRow(1);
// 写入各个字段的列头名称
for (int i = 0; i < fields.size(); i++)
{
Field field = fields.get(i);
Excel attr = field.getAnnotation(Excel.class);
// 创建列
cell = row.createCell(i);
// 设置列中写入内容为String类型
cell.setCellType(CellType.STRING);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
if (attr.name().indexOf("注：") >= 0)
{
Font font = wb.createFont();
font.setColor(HSSFFont.COLOR_RED);
cellStyle.setFont(font);
//cellStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());
sheet.setColumnWidth(i, 6000);
}
else
{
Font font = wb.createFont();
// 粗体显示
font.setBold(true);
// 选择需要用到的字体格式
cellStyle.setFont(font);
//cellStyle.setFillForegroundColor(22);
//cellStyle.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());
// 设置列宽
sheet.setColumnWidth(i, (int) ((attr.width() + 0.72) * 256));
row.setHeight((short) (attr.height() * 20));
}
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
//cell.setCellStyle(cellStyle);
// 写入列名
cell.setCellValue(attr.name());
// 如果设置了提示信息则鼠标放上去提示.
if (StringUtils.isNotEmpty(attr.prompt()))
{
// 这里默认设了2-101列提示.
//setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, i, i);
}
// 如果设置了combo属性则本列只能选择不能输入
if (attr.combo().length > 0)
{
// 这里默认设了2-101列只能选择不能输入.
//setXSSFValidation(sheet, attr.combo(), 1, 100, i, i);
}
}
if (Excel.Type.EXPORT.equals(type))
{
fillExcelData(index, row, cell);
}
}
String filename = encodingFilename(sheetName);
out = new FileOutputStream(getAbsoluteFile(filename));
wb.write(out);
return AjaxResult.success(filename);
}
catch (Exception e)
{
log.error("导出Excel异常{}", e.getMessage());
throw new BusinessException("导出Excel失败，请联系网站管理员！");
}
finally
{
if (wb != null)
{
try
{
wb.close();
}
catch (IOException e1)
{
e1.printStackTrace();
}
}
if (out != null)
{
try
{
out.close();
}
catch (IOException e1)
{
e1.printStackTrace();
}
}
}
}
/**
* 创建工作表
*
* @param sheetName，指定Sheet名称
* @param sheetNo sheet数量
* @param index 序号
*/
public void createSheet(double sheetNo, int index)
{
this.sheet = wb.createSheet();
// 设置工作表的名称.
if (sheetNo == 0)
{
wb.setSheetName(index, sheetName);
}
else
{
wb.setSheetName(index, sheetName + index);
}
}
/**
* 填充excel数据
*
* @param index 序号
* @param row 单元格行
* @param cell 类型单元格
*/
public void fillExcelData(int index, Row row, Cell cell)
{
int startNo = index * sheetSize;
int endNo = Math.min(startNo + sheetSize, list.size());
// 写入各条记录,每条记录对应excel表中的一行
CellStyle cs = wb.createCellStyle();
cs.setAlignment(HorizontalAlignment.CENTER);
cs.setVerticalAlignment(VerticalAlignment.CENTER);
for (int i = startNo; i < endNo; i++)
{
row = sheet.createRow(i + 1 - startNo);
// 得到导出对象.
T vo = (T) list.get(i);
for (int j = 0; j < fields.size(); j++)
{
// 获得field.
Field field = fields.get(j);
// 设置实体类私有属性可访问
field.setAccessible(true);
Excel attr = field.getAnnotation(Excel.class);
try
{
// 设置行高
row.setHeight((short) (attr.height() * 20));
// 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
if (attr.isExport())
{
// 创建cell
cell = row.createCell(j);
cell.setCellStyle(cs);
if (vo == null)
{
// 如果数据存在就填入,不存在填入空格.
cell.setCellValue("");
continue;
}
// 用于读取对象中的属性
Object value = getTargetValue(vo, field, attr);
String dateFormat = attr.dateFormat();
String readConverterExp = attr.readConverterExp();
if (StringUtils.isNotEmpty(dateFormat))
{
cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
}
else if (StringUtils.isNotEmpty(readConverterExp))
{
cell.setCellValue(convertByExp(String.valueOf(value), readConverterExp));
}
else
{
cell.setCellType(CellType.STRING);
// 如果数据存在就填入,不存在填入空格.
cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
}
}
}
catch (Exception e)
{
log.error("导出Excel失败{}", e.getMessage());
}
}
}
}
/**
* 获取bean中的属性值
*
* @param vo 实体对象
* @param field 字段
* @param excel 注解
* @return 最终的属性值
* @throws Exception
*/
private Object getTargetValue(T vo, Field field, Excel excel) throws Exception
{
Object o = field.get(vo);
if (StringUtils.isNotEmpty(excel.targetAttr()))
{
String target = excel.targetAttr();
if (target.indexOf(".") > -1)
{
String[] targets = target.split("[.]");
for (String name : targets)
{
o = getValue(o, name);
}
}
else
{
o = getValue(o, target);
}
}
return o;
}
/**
* 以类的属性的get方法方法形式获取值
*
* @param o
* @param name
* @return value
* @throws Exception
*/
private Object getValue(Object o, String name) throws Exception
{
if (StringUtils.isNotEmpty(name))
{
Class<?> clazz = o.getClass();
String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
Method method = clazz.getMethod(methodName);
o = method.invoke(o);
}
return o;
}
/**
* 得到所有定义字段
*/
private void createExcelField()
{
this.fields = new ArrayList<Field>();
List<Field> tempFields = new ArrayList<>();
Class<?> tempClass = clazz;
tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
while (tempClass != null)
{
tempClass = tempClass.getSuperclass();
if (tempClass != null)
tempFields.addAll(Arrays.asList(tempClass.getDeclaredFields()));
}
putToFields(tempFields);
}
/**
* 放到字段集合中
*/
private void putToFields(List<Field> fields)
{
for (Field field : fields)
{
Excel attr = field.getAnnotation(Excel.class);
if (attr != null && (attr.type() == Excel.Type.ALL || attr.type() == type))
{
this.fields.add(field);
}
}
}
/**
* 创建一个工作簿
*/
public void createWorkbook()
{
this.wb = new SXSSFWorkbook(500);
}
/**
* 解析导出值 0=男,1=女,2=未知
*
* @param propertyValue 参数值
* @param converterExp 翻译注解
* @return 解析后值
* @throws Exception
*/
public static String convertByExp(String propertyValue, String converterExp) throws Exception
{
try
{
String[] convertSource = converterExp.split(",");
for (String item : convertSource)
{
String[] itemArray = item.split("=");
if (itemArray[0].equals(propertyValue))
{
return itemArray[1];
}
}
}
catch (Exception e)
{
throw e;
}
return propertyValue;
}
/**
* 反向解析值 男=0,女=1,未知=2
*
* @param propertyValue 参数值
* @param converterExp 翻译注解
* @return 解析后值
* @throws Exception
*/
public static String reverseByExp(String propertyValue, String converterExp) throws Exception
{
try
{
String[] convertSource = converterExp.split(",");
for (String item : convertSource)
{
String[] itemArray = item.split("=");
if (itemArray[1].equals(propertyValue))
{
return itemArray[0];
}
}
}
catch (Exception e)
{
throw e;
}
return propertyValue;
}
/**
* 编码文件名
*/
public String encodingFilename(String filename)
{
filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
return filename;
}
/**
* 获取下载路径
*
* @param filename 文件名称
*/
public String getAbsoluteFile(String filename)
{
String downloadPath = "D:/profile/" + filename;
File desc = new File(downloadPath);
if (!desc.getParentFile().exists())
{
desc.getParentFile().mkdirs();
}
return downloadPath;
}
}
/**
* 文件上传
*/
$(function(){
$('#btnUpload').click(function(){
var file = $('#file').val();
console.log(file)
if(file == null || file == ""){
alert('请选择EXcel文件')
return;
}else{
var formData = new FormData($( "#uploadForm" )[0]);
var name = $("#file").val();
var reg = /^.*\.(?:xls|xlsx)$/i;//文件名可以带空格
if (!reg.test(name)){
alert("请上传.xls格式的文件!");
return;
}
$.ajax({
url:basePath+'dqfnPlanimportAction!importData.action',
type:'post',
data:formData,
cache: false,
contentType: false,
processData: false,
scriptCharset: 'utf-8',
success: function (data) {
alert('导入成功');
window.location.reload();
},
error: function (returndata) {
alert('导入失败')
$("#msg").html(returndata);
$('#btnUpload').show();
$('#loading_msg_msg').hide();
}
});
}
})
})
function toJump(){
var page=$('#page').val();
window.location.href="dqfnPlanimportAction!toListUI.action?page="+page;
}
/**
* 计划下单
* @param tf_id
*/
function toConfirm(tf_id){
var flag = confirm('确认下单吗?');
if(flag){
$.ajax({
type : 'POST',
dataType : "json",
url : basePath+"dqfnPlanimportAction!toConfirm.action",
data :{'tf_id':tf_id},
success : function(data) {
//重新判断返回值,如果没问题返回0,如果有问题,直接返回其它信息
if(data.code == 500){
alert("操作失败："+data.msg);
}
},
error:function(err){
console.log("err");
console.log(err);
}
});
}
}
<%@page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.net.URLDecoder"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title></title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<link rel="stylesheet" type="text/css" href="<%=basePath %>css/tab.css">
<script>
var basePath = "<%=basePath%>";
</script>
<script type="text/javascript" src="<%=basePath %>js/tab.js"></script>
<script type="text/javascript" src="<%=basePath %>js/jquery-1.8.0.min.js?v=22"></script>
<script type="text/javascript" src="<%=basePath %>system/plan/dqfn/import/list.js?v=22s"></script>
<style>
input,select,textarea{
outline:none;
}
input:-webkit-autofill{-webkit-box-shadow: 0 0 0px 1000px white inset;}
.input,.select {
margin-left:5px;
border-style:none;
border-bottom:1px dashed #000;
margin-left:10px;
width:300px;
}
table{
margin-top:5px;
margin-left:5px;
font-size:12px;
text-align:center;
}
table tr td{
height:25px;
line-height:25px;
}
.tool{
font-size:20px;
height:35px;
line-height:35px;
font-weight:bolder;
text-align: center;
float:left;
color:blue;
margin-left: 30px;
}
.STYLE4{
margin-left:10px;
}
.add{
height:25px;
width:50px;
margin-top:5px;
}
.tr:HOVER {
background-color:#E5E09B;
cursor:pointer;
}
.btn{
background-color:#FFF;
border:1px solid #CDCDCD;
height:24px; width:70px;
float:left;
padding-top:5px;
padding-bottom:5px;
}
#tf_slices_number{
overflow: hidden;
white-space: nowrap;
text-overflow: ellipsis;
}
#tf_remarks{
overflow:hidden;
white-space:nowrap;
text-overflow:ellipsis;
}
</style>
</head>
<body>
<form id="uploadForm" enctype="multipart/form-data">
<table cellpadding="0" cellspacing="1" width="3200" bgcolor="#000">
<tr bgcolor="#fff">
<c:choose>
<c:when test="${tf_flag == 'N' }">
<td colspan="35" align="center">
<Div style="color:blue;font-weight:bolder;font-size:14px;">未下单列表</Div>
</td>
</c:when>
<c:otherwise>
<td colspan="35" align="center">
<Div style="color:blue;font-weight:bolder;font-size:14px;">已下单列表</Div>
</td>
</c:otherwise>
</c:choose>
</tr>
<tr bgcolor="#fff">
<td colspan="4" align="left" style="padding-left:5px;">
<c:choose>
<c:when test="${tf_flag == 'N' }">
<a href="dqfnPlanimportAction!toListUI.action?page.page=1&tf_flag=Y">已下单列表</a> |
</c:when>
<c:otherwise>
<a href="dqfnPlanimportAction!toListUI.action?page.page=1&tf_flag=N">未下单列表</a> |
</c:otherwise>
</c:choose>
<a onclick="{if(confirm('确认清除所有订单信息吗?')){return true;}return false;}"href="dqfnPlanimportAction!toRemove.action">清空列表</a> |
<a onclick="{if(confirm('确认批量下单吗?')){return true;}return false;}" href="dqfnPlanimportAction!toBatConfirm.action">批量下单</a>
</td>
<td colspan="31" align="left">
请选择要上传的Excel文件<input id="file" name="file" type="file">
<button id="btnUpload" type="button">导入</button> <font color="red">备注:只允许上传扩展名库.xls的文件上传数据时，请在此外下载指定的<a href="<%=basePath %>images/excel/DQFNPlanImport.xls" style="color:red;font-weight:bolder;">Excel模板</a> </font>
</td>
</tr>
<tr bgcolor="#29A2CE" height="25">
<td width="40" rowspan="2">序号</td>
<td width="120" rowspan="2">管理</td>
<td width="80" rowspan="2">任务书号</td>
<td width="70" rowspan="2">ASP订单号</td>
<td width="70" rowspan="2">工单类型</td>
<td width="140" rowspan="2">封装形式</td>
<td width="70" rowspan="2">成品料号</td>
<td width="90" rowspan="2">成品型号</td>
<!-- 晶圆a -->
<td colspan="4">晶圆A</td>
<!-- 晶圆b -->
<td colspan="4">晶圆B</td>
<!-- 晶圆c -->
<td colspan="4">晶圆C</td>
<!-- 晶圆d -->
<td colspan="4">晶圆D</td>
<!-- 晶圆d -->
<td colspan="4">打标</td>
<td width="90" rowspan="2">Order Code</td>
<td width="100" rowspan="2">ID</td>
<td width="80" rowspan="2">DPN</td>
<td width="70" rowspan="2">下单数量</td>
<td width="100" rowspan="2">交期</td>
<td width="100" rowspan="2">工单颜色</td>
<td width="140" rowspan="2">备注</td>
</tr>
<tr bgcolor="#29A2CE">
<!-- 晶圆a -->
<td width="80">型号</td>
<td width="80">批号</td>
<td width="80">片数</td>
<td width="120">备注</td>
<!-- 晶圆b -->
<td width="80">型号</td>
<td width="80">批号</td>
<td width="80">片数</td>
<td width="120">备注</td>
<!-- 晶圆c -->
<td width="80">型号</td>
<td width="80">批号</td>
<td width="80">片数</td>
<td width="120">备注</td>
<!-- 晶圆d -->
<td width="80">型号</td>
<td width="80">批号</td>
<td width="80">片数</td>
<td width="120">备注</td>
<!-- 打标 -->
<td width="80">LOGO</td>
<td width="80">型号</td>
<td width="80">年周</td>
<td width="120">其它</td>
</tr>
<c:set scope="page" var="index" value="${(pageNo-1)*20 }"></c:set>
<c:forEach items="${list }" var="n">
<c:set scope="page" var="index" value="${index+1}"></c:set>
<tr class="tr" bgcolor="#fff">
<td>${index}</td>
<td>
<%--
<a onclick="toConfirm('${n.tf_id}');" href="javascript:void(0);">确认下单</a>|
--%>
<c:choose>
<c:when test="${n.tf_flag == 'N' }">
<a onclick="{if(confirm('确认下单吗?')){return true;}return false;}" href="<%=basePath %>dqfnPlanimportAction!toConfirm.action?tf_id=${n.tf_id }">确认下单</a>|
<a onclick="{if(confirm('确认删除订单信息吗?')){return true;}return false;}" href="<%=basePath %>dqfnPlanimportAction!toDel.action?tf_id=${n.tf_id }">删除</a>
</c:when>
<c:otherwise>
<a href="<%=basePath %>dqfnPlanimportAction!toBack.action?tf_id=${n.tf_id }">退回</a>
</c:otherwise>
</c:choose>
</td>
<td title="${n.tf_taskno}">${n.tf_taskno}</td>
<td>${n.tf_dingdan}</td>
<td>${n.tf_type }</td>
<td>${n.tf_encfor}</td>
<td>${n.tf_product_number}</td>
<td id="tf_product_model" title="${n.tf_product_model}">
<c:choose>
<c:when test="${fn:length(n.tf_product_model) > 10 }">
${fn:substring(n.tf_product_model,0,10)}...
</c:when>
<c:otherwise>
${n.tf_product_model}
</c:otherwise>
</c:choose>
</td>
<td>${n.tf_wafer_a_xh }</td>
<td>${n.tf_wafer_a_ph }</td>
<td>${n.tf_wafer_a_ps }</td>
<td id="tf_wafer_a_bz" title="${n.tf_wafer_a_bz}">
<c:choose>
<c:when test="${fn:length(n.tf_wafer_a_bz)> 10}">
${fn:substring(n.tf_wafer_a_bz,0,10)}...
</c:when>
<c:otherwise>
${n.tf_wafer_a_bz }
</c:otherwise>
</c:choose>
</td>
<td>${n.tf_wafer_b_xh }</td>
<td>${n.tf_wafer_b_ph }</td>
<td>${n.tf_wafer_b_ps }</td>
<td id="tf_wafer_b_bz" title="${n.tf_wafer_b_bz}">
<c:choose>
<c:when test="${fn:length(n.tf_wafer_b_bz) > 10 }">
${fn:substring(n.tf_wafer_b_bz,0,10)}...
</c:when>
<c:otherwise>
${n.tf_wafer_b_bz }
</c:otherwise>
</c:choose>
</td>
<td>${n.tf_wafer_c_xh }</td>
<td>${n.tf_wafer_c_ph }</td>
<td>${n.tf_wafer_c_ps }</td>
<td>
<c:choose>
<c:when test="${fn:length(n.tf_wafer_c_bz) > 10 }">
${fn:substring(n.tf_wafer_c_bz,0,10)}...
</c:when>
<c:otherwise>
${n.tf_wafer_c_bz }
</c:otherwise>
</c:choose>
</td>
<td>${n.tf_wafer_d_xh }</td>
<td>${n.tf_wafer_d_ph }</td>
<td>${n.tf_wafer_d_ps }</td>
<td id="tf_wafer_d_bz" title="${n.tf_wafer_d_bz}">
<c:choose>
<c:when test="${fn:length(n.tf_wafer_d_bz) > 10 }">
${fn:substring(n.tf_wafer_d_bz,0,10)}...
</c:when>
<c:otherwise>
${n.tf_wafer_d_bz }
</c:otherwise>
</c:choose>
</td>
<td>${n.tf_mark_a }</td>
<td>${n.tf_mark_b }</td>
<td>${n.tf_mark_c }</td>
<td>${n.tf_mark_d }</td>
<td>${n.tf_p1 }</td>
<td>${n.tf_p2 }</td>
<td>${n.tf_p3 }</td>
<td>${n.tf_number }</td>
<td>${n.tf_delivery_time}</td>
<td>${n.tf_color }</td>
<td id="tf_remarks" title="${n.tf_remarks}">
<c:choose>
<c:when test="${fn:length(n.tf_remarks) > 40 }">
${fn:substring(n.tf_remarks,0,40) }...
</c:when>
<c:otherwise>
${n.tf_remarks}
</c:otherwise>
</c:choose>
</td>
</tr>
</c:forEach>
<tr bgcolor="#fff">
<td colspan="35">
<div class="STYLE4" align="left">
共有${total} 条记录，当前第 ${pageNo}/${countPage}页
<c:if test="${pageNo > 1 }">
<a href="dqfnPlanimportAction!toListUI.action?tf_flag=${tf_flag}&page=1">首页</a>
<a href="dqfnPlanimportAction!toListUI.action?tf_flag=${tf_flag}&page=${pageNo - 1}">上一页</a>
</c:if>
<c:if test="${pageNo < countPage }">
<a href="dqfnPlanimportAction!toListUI.action?tf_flag=${tf_flag}&page=${pageNo + 1}">下一页</a>
<a href="dqfnPlanimportAction!toListUI.action?tf_flag=${tf_flag}&page=${countPage}">尾页</a>
</c:if>
跳转到
<input type="number" id="page" size="8" min="1" max="10000" name="page" >
页 <input type="button" onclick="toJump();" id="inputForm" name="inputForm" value="跳转" />
</div>
</td>
</tr>
</table>
</form>
<div style="height:40px;line-height:40px;border:0px solid red;font-size:12px;margin-left:3px;color:blue;">
色彩参考：<input onchange="setColor()" style="margin-left:10px;outline:0; width:100px;" class="color" id="tf_color" name="tf_color" type="color" value="${protype.tf_color == null ? '#ffffff' : protype.tf_color }"/> <span id="color">#ffffff</span>
</div>
</body>
</html>
<script>
function toJump(){
var page=$('#page').val();
window.location.href="dqfnPlanimportAction!toListUI.action?tf_flag=${tf_flag}&page="+page;
}
function setColor(){
$('#color').html($('#tf_color').val());
}
</script>
883
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
