package com.yudianbank.front.util;
import java.awt.Image;
import java.awt.image.BufferedImage;
import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLDecoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Map.Entry;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
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 com.yudianbank.common.util.ResourceUtil;
import com.yudianbank.front.model.Person;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**
* 解析excel2* @return
* @throws IOException
*/
public class ReadExcel {
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
Person person = null;
List<Person> list = new ArrayList<Person>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
person = new Person();
HSSFCell name = hssfRow.getCell(0);
HSSFCell card_no = hssfRow.getCell(1);
person.setName(getValue(name));
person.setCard_id(getValue(card_no));
list.add(person);
}
}
}
return list;
}
private static String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
}
/**
* 生成excel
* @throws IOException
*/
public static void createExcel(List<Person>list) throws IOException{
pertery = ResourceUtil.getResourceAsProperties("weixin.properties");
String outpath = pertery.getProperty("outexcel_url");
HSSFWorkbook wb = new HSSFWorkbook(); // 创建一个webwork,对应一个excel文件
HSSFSheet sheet = wb.createSheet("person表");// 在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFRow row = sheet.createRow((int) 0); // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFCellStyle style = wb.createCellStyle(); // 创建单元格，并设置值表头 设置表头居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("name");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("card_id");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("result");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("msg");
cell.setCellStyle(style);
for(int i=0;i<list.size();i++){
row = sheet.createRow((int) i + 1);
Person p = (Person) list.get(i);
row.createCell((short) 0).setCellValue(p.getName()); // 创建单元格，并设置值
row.createCell((short) 1).setCellValue(p.getCard_id());
row.createCell((short) 2).setCellValue(p.getResult());
row.createCell((short) 3).setCellValue(p.getMsg());
}
FileOutputStream f = new FileOutputStream(outpath);// 输出
wb.write(f);
f.close();
}
/**
* 数据批量导入数据库
*
* @throws Exception
*/
@RequestMapping(value = "/save", method = { RequestMethod.POST })
public void saveDate() throws Exception {
pertery = ResourceUtil.getResourceAsProperties("weixin.properties");
String path = pertery.getProperty("readexcel_url");
List<Person> listexcel = readExcel.readXls(path);
List<Person> list = person.getPerson();
System.out.println(list.size());
for (int i = 0; i < list.size(); i++) {
Person p = list.get(i);
String result = dasPengYuanImpl.getIdCardInfo(p.getCard_id(), p.getName());// 调用身份认证信息接口
JsonUtil jsonUtil = new JsonUtil(result.toString());
String re = jsonUtil.getStringByJson("cisReport.policeCheckInfo.item.result");
p.setResult(re);
person.savePerson(p);
}
}