package com.genersoft.cbms.ysbz.ExcelDr.cmd; import com.genersoft.cbms.ysbz.ExcelDr.dao.ExcelDrDao;
import com.genersoft.cbms.ysbz.ExcelDr.dao.IExcelDrDao;
import com.genersoft.cbms.ysbz.ExcelDr.domain.IExcelDrDomain;
import com.genersoft.cbms.ysbz.ExcelDr.entity.ExcelDr;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.loushang.bsp.security.context.GetBspInfo;
import org.loushang.bsp.share.organization.OrganFactory;
import org.loushang.next.dao.DaoFactory;
import org.loushang.next.data.DataSet;
import org.loushang.next.data.ParameterSet;
import org.loushang.next.data.Record;
import org.loushang.next.upload.UploadFile;
import org.loushang.next.web.cmd.BaseAjaxCommand;
import org.loushang.sca.ScaComponentFactory; import java.io.*;
import java.net.ServerSocket;
import java.util.*; /**
* Created by lyx on 2016/10/18.
*/
public class ExcelDrCommand extends BaseAjaxCommand {
private static IExcelDrDomain excelDomain = ScaComponentFactory.getService(IExcelDrDomain.class, "excelDomain/excelDomain");
IExcelDrDao exceldao = (IExcelDrDao) DaoFactory.getDao(ExcelDrDao.class.getName()); String dynm; /**
* excel导入
*/
public void importExcel() {
//用来存插到bzsj表里的数据
//List<ExcelDr> itemList = new ArrayList<ExcelDr>();
ExcelDr item = new ExcelDr(); //获取用户名称
String organ_id = GetBspInfo.getBspInfo().getCorporationOrganId();//组织ID
String organ_name;
if (organ_id == null) {
organ_name = "%";
} else {
organ_name = OrganFactory.getIOrganProvider().getOrganByOrganId(organ_id).getOrganName();//组织名称
}
item.setFcsjUser(organ_id); String slnm = (String) getParameter("slnm");
item.setFcsjSlnm(slnm);
//前台选中的组织的内码编号和名称
String zznm = (String) getParameter("zznm");
String zzbh = (String) getParameter("zzbh");
String zzmc = (String) getParameter("zzmc");
//是否选中按组织导入
Boolean ifcheck = (Boolean) getParameter("ifcheck");
//前台选中的目标类型的内码编号和名称
String faMblx = (String) getParameter("faMblx");
String mblxmc = (String) getParameter("mblxmc");
String mblxbh = (String) getParameter("mblxbh");
//获取前台选中了哪些报表
String[] bbbhs = (String[]) getParameter("bbbhs"); //不按组织导入时，获取前台选中了哪些组织
String[] zzbhs = (String[]) getParameter("zzbhs"); //解析excel
Record[] records = (Record[]) getParameter("records");
if (records == null || records.length < 1)
return;
Record record = records[0];
UploadFile file = (UploadFile) record.get("file");
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
// 得到工作表
if (inputStream == null) {
return;
}
//文件名
String url = file.getFileName();
//文件的后缀名
String suffix = url.substring(url.lastIndexOf("."));
// HSSFWorkbook book = null;
Workbook book=null;
/* try {
//如果是xlsx格式，则这一步会报错
book = new HSSFWorkbook(inputStream);
} catch (IOException e) {
e.printStackTrace();
}*/
try {
if(".xls".equals(suffix)){
//支持07版本以前的excel
book= new HSSFWorkbook(inputStream);
}else if(".xlsx".equals(suffix)){
//支持07版本以后的excel
book = new XSSFWorkbook(inputStream);
}else{
System.out.println("不支持的文件类型！");
return;
}
} catch (IOException e) {
e.printStackTrace();
}
if (book == null) {
return;
}
//得到一共有几个sheet
int sheetnum = book.getNumberOfSheets(); //sheet页循环
for (int i = 0; i < sheetnum; i++) {
boolean ifbbexist = false;
boolean ifzzexist = false;
boolean ifbbzzexist = false;
Sheet sheet = null;
sheet = book.getSheetAt(i);
if (sheet != null) {
//获取sheet页的名称
String sheetName = sheet.getSheetName();
//获取报表中的最后一行的行号，则总行数等于它加1
int allrow = sheet.getLastRowNum() + 1; String sheetzzbh = "";
String[] sheetnmmc = new String[2];
if (ifcheck) {
//如果是安组织导入，将得到的sheet页名称分开，以便获取报表编号
String[] sheetNames = sheetName.split("\\.");
String bbbh = sheetNames[0];
for (int n = 0; n < bbbhs.length; n++) {
if (bbbh.equals(bbbhs[n])) {
ifbbexist = true;
}
}
if (ifbbexist) {
dynm = getDynm(bbbh);
item.setFcsjZznm(zznm);
item.setFcsjZzbh(zzbh);
item.setFcsjZzmc(zzmc);
}
} else {
//将得到的sheet页名称分开，以便获取报表编号
String[] sheetNames = sheetName.split("\\.");
String bbbh = sheetNames[0];
//将得到的sheet页名称分开，以便获取组织编号
sheetzzbh = sheetNames[1].substring(sheetNames[1].indexOf("(") + 1, sheetNames[1].indexOf(")"));
//判断此sheet页的报表编号是否是选中的
for (int b = 0; b < bbbhs.length; b++) {
if (bbbh.equals(bbbhs[b])) {
ifbbexist = true;
}
}
//判断此sheet页的组织编号是否是选中的
for (int z = 0; z < zzbhs.length; z++) {
if (sheetzzbh.equals(zzbhs[z])) {
ifzzexist = true;
}
}
if (ifbbexist && ifzzexist) {
ifbbzzexist = true;
dynm = getDynm(bbbh);
sheetnmmc = getZznmmc(sheetzzbh);
item.setFcsjZznm(sheetnmmc[0]);
item.setFcsjZzbh(sheetzzbh);
item.setFcsjZzmc(sheetnmmc[1]);
}
}
//1.(按组织导入）如果sheet页中的报表编号跟前台选中的编号相等，才能导入
//2.(不按组织导入）sheet页中的报表编号和组织编号跟前台选中的编号相等，才能导入
if ((ifbbexist && ifcheck) || ifbbzzexist) {
//获取该报表是否是两栏表头的表格
List<String> zhcs = getIfzh(dynm);
String ifzh = zhcs.get(0);
String kzhs = zhcs.get(1);
int kzh = Integer.parseInt(kzhs);
//获取该报表数据库里一共有几列
int tableCol = getTableCol(dynm);
//获取内容行从哪一行开始
int rownum = getRownum(dynm);
//内容行的行号（从1开始）
String mxnm = getMxnm(dynm);
DataSet dsc = getSjl(dynm);
DataSet dsr = getXxl(dynm);
int xxls = getXxls(dynm);
int hzbbStat = getStat(dynm, item);
//编制行里的行记录
List<HashMap<String, Object>> recordsList = getRecList(dynm,mxnm,item); int footerRow = getFooternum(dynm); //两栏表
if(ifzh.equals("1")){
int rowXh = 1;
for (int j = rownum; j < rownum+kzh-1; j++) {
int firstLan =1;
//cell单元格的值
String value = "";
Map<Integer, String> dataMap = new HashMap<Integer, String>();
int col = 0;
// 得到j的那一行
Row rowi = sheet.getRow(j);
// 得到该行的所有列
Iterator<Cell> cellTitle = rowi.cellIterator();
// 循环标题所有的列
while (cellTitle.hasNext()) {
if(0<firstLan && firstLan<=tableCol){
Cell cell = (Cell) cellTitle.next();
int type = cell.getCellType();
switch (type) {
case 0:
Double dValue = (Double) cell.getNumericCellValue();
value = dValue.toString();
break;
case 1:
value = cell.getStringCellValue();
break;
case 3:
value = "";
break;
}
dataMap.put(col, value);
col++;
}else{
break;
}
firstLan++;
}
Date date = new Date();
item.setCreatedtime(date);
item.setLastmodifiedtime(date);
exceldao.importExcel(dynm, dataMap, item, rowXh,mxnm,recordsList,xxls,hzbbStat,dsc,dsr);
rowXh++;
}
for (int j = rownum; j <= rownum+recordsList.size()-kzh; j++) {
int secontLan =1;
//cell单元格的值
String value = "";
Map<Integer, String> dataMap = new HashMap<Integer, String>();
int col = 0;
// 得到j的那一行
Row rowi = sheet.getRow(j);
// 得到该行的所有列
Iterator<Cell> cellTitle = rowi.cellIterator();
// 循环标题所有的列
while (cellTitle.hasNext()) {
Cell cell = (Cell) cellTitle.next();
if(tableCol<secontLan && secontLan<=tableCol*2){
int type = cell.getCellType();
switch (type) {
case 0:
Double dValue = (Double) cell.getNumericCellValue();
value = dValue.toString();
break;
case 1:
value = cell.getStringCellValue();
break;
case 3:
value = "";
break;
}
dataMap.put(col, value);
col++;
}
if(secontLan>tableCol*2){
break;
}
secontLan++;
}
Date date = new Date();
item.setCreatedtime(date);
item.setLastmodifiedtime(date);
exceldao.importExcel(dynm, dataMap, item, rowXh,mxnm,recordsList,xxls,hzbbStat,dsc,dsr);
rowXh++;
}
}else{
int rowXh = 1;
//正常表
for (int j = rownum; j < rownum+recordsList.size(); j++) {
//cell单元格的值
String value = "";
Map<Integer, String> dataMap = new HashMap<Integer, String>();
int col = 0;
// 得到j的那一行
Row rowi = sheet.getRow(j);
// 得到该行的所有列
Iterator<Cell> cellTitle = rowi.cellIterator();
// 循环标题所有的列
while (cellTitle.hasNext()) {
Cell cell = (Cell) cellTitle.next();
/* cell.getCellType()返回的类型：
int CELL_TYPE_NUMERIC = 0;
int CELL_TYPE_STRING = 1;
int CELL_TYPE_FORMULA = 2;
int CELL_TYPE_BLANK = 3;
int CELL_TYPE_BOOLEAN = 4;
int CELL_TYPE_ERROR = 5;*/
int type = cell.getCellType();
switch (type) {
case 0:
Double dValue = (Double) cell.getNumericCellValue();
value = dValue.toString();
break;
case 1:
value = cell.getStringCellValue();
break;
case 3:
value = "";
break;
}
dataMap.put(col, value);
col++;
}
Date date = new Date();
item.setCreatedtime(date);
item.setLastmodifiedtime(date);
exceldao.importExcel(dynm, dataMap, item, rowXh,mxnm,recordsList,xxls,hzbbStat,dsc,dsr);
/*exceldao.importExcel(dynm,value,item);*/
rowXh++;
}
}
}
}
}
} //获取报表的对应内码
public String getDynm(String bbbh) {
String txnm = (String) getParameter("txnm");
return exceldao.getDynm(txnm, bbbh);
}
public String getMxnm(String dynm) {
return exceldao.getMxnm(dynm);
} //获取报表是否两栏表头
public List<String> getIfzh(String dynm){
return exceldao.getIfzh(dynm);
}
//获取报表一共有几列
public int getTableCol(String dynm){
return exceldao.getTableCol(dynm);
} //根据sheet的组织编号获取组织的内码和名称
public String[] getZznmmc(String sheetzzbh) {
String txnm = (String) getParameter("sheetzzbh");
return exceldao.getZznmmc(sheetzzbh);
} //确定报表从第几行开始才是数据行（内容行）
public int getRownum(String dynm) {
return exceldao.getRownum(dynm);
}
public DataSet getSjl(String dynm) {
return exceldao.getSjl(dynm);
}
public DataSet getXxl(String dynm) {
return exceldao.getXxl(dynm);
}
public int getXxls(String dynm) {
return exceldao.getXxls(dynm);
}
public int getStat(String dynm,ExcelDr item) {
return exceldao.getStat(dynm,item);
} //确定报表从第几行开始才是数据行（内容行）
public int getFooternum(String dynm) {
return exceldao.getFooternum(dynm);
}
public List<HashMap<String, Object>> getRecList(String dynm,String mxnm,ExcelDr item) {
return exceldao.getRecList(dynm,mxnm,item);
} /**
* @param
* @return void 返回类型
* @throws
* @Title: getCS
* @Description: 获取当前用户所属组织的信息
*/
public void getCS() { String organ_id = GetBspInfo.getBspInfo().getCorporationOrganId();//组织ID if (organ_id == null) {
setReturn("organ_id", "%");
} else {
String organ_name = OrganFactory.getIOrganProvider().getOrganByOrganId(organ_id).getOrganName();//组织姓名
setReturn("organ_id", organ_id);
}
} public void getParentOrgans() {
String organId = (String) getParameter("organId");
List<String> parentOrgans = new ArrayList<String>();
parentOrgans = exceldao.getParentOrgans(organId);
setReturn("organList", parentOrgans);
}
public void getFaslnm(){
ParameterSet pset = getParameterSet();
String faslnm= exceldao.getFaslnm(pset);
setReturn("faslnm", faslnm);
}
}
这些代码，必须要引入相应jar包才可以。