/**
* 导出台账
*/
@RequestMapping(params = "ExportXltz")
@ResponseBody
public void ExportXltz(HttpServletRequest request, ModelMap modelMap, HttpServletResponse response,
OutputStream output) throws UnsupportedEncodingException {
String invoiceno = request.getParameter("invoiceno");//发票号
String sendTime_begin = request.getParameter("sendTime_begin");//发送日期
String sendTime_end = request.getParameter("sendTime_end");//发送日期
String orderSupply = request.getParameter("orderSupply");//代理商
String zdDate_begin = request.getParameter("zdDate_begin");//账单日期
String zdDate_end = request.getParameter("zdDate_end");//账单日期
String declaredate_begin = request.getParameter("declaredate_begin");//申报日期开始时间
String declaredate_end = request.getParameter("declaredate_end");//申报日期结束时间
String wheresql = " ";
if(invoiceno!=null && !invoiceno.equals("")) {
wheresql += " and a.invoice_code='" + invoiceno + "'";
}
if(sendTime_begin!=null && !sendTime_begin.equals("")) {
wheresql += " and a.send_time >='" + sendTime_begin + "'";
}
if(sendTime_end!=null && !sendTime_end.equals("")) {
wheresql += " and a.send_time <='" + sendTime_end + "'";
}
if(orderSupply!=null && !orderSupply.equals("")) {
wheresql += " and c.order_supplyid='" + orderSupply + "'";
}
if(zdDate_begin!=null && !zdDate_begin.equals("")) {
wheresql += " and c.zdDate >='" + zdDate_begin + "'";
}
if(zdDate_end!=null && !zdDate_end.equals("")) {
wheresql += " and c.zdDate <='" + zdDate_end + "'";
}
if(declaredate_begin!=null && !declaredate_begin.equals("")) {
wheresql += " and b.declaration_data >='" + declaredate_begin + "'";
}
if(declaredate_end!=null && !declaredate_end.equals("")) {
wheresql += " and b.declaration_data <='" + declaredate_end + "'";
}
String sql = " select * " +
" from dec_order a " +
" left join dec_main b on (b.erp_No = a.invoice_code)" +
" left join (select Invoiceno,order_supply,order_supplyid,zdDate" +
" from ldc_order_tax " +
" group by Invoiceno) c on (c.Invoiceno = a.invoice_code)"+
wheresql + " order by b.create_date desc ";
String lujing = request.getSession().getServletContext().getRealPath("/");
String lujing1 = lujing + "export\\template\\jkfymx.xlsx";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
InputStream in;
try {
in = new FileInputStream(new File(lujing1));
XSSFWorkbook work = null;
work = new XSSFWorkbook(in);
this.ExportXltzOut(request, response, work, maps);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 导出
*/
public void ExportXltzOut(HttpServletRequest request, HttpServletResponse response, XSSFWorkbook work, List<Map<String, Object>> list) {
XSSFSheet sheetAt = work.getSheetAt(0);
XSSFCellStyle setBorder = work.createCellStyle();
setBorder.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
setBorder.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
setBorder.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
Map<String, Object> map = new HashMap<>();
String invoice_code_old = "";
int cnt = 0;
for(int i=0;i<list.size();i++){
if(i<list.size()){
map = list.get(i);
}
String invoice_code = String.valueOf(map.get("invoice_code"));
XSSFRow row = sheetAt.createRow(i+2);
//物流公司名称
if(map.get("order_supply") !=null) {
row.createCell(0).setCellValue(String.valueOf(map.get("order_supply")));
row.getCell(0).setCellStyle(setBorder);
}else {
row.createCell(0).setCellValue("");
row.getCell(0).setCellStyle(setBorder);
}
//进口发票号码
if(map.get("invoice_code") !=null) {
row.createCell(1).setCellValue(String.valueOf(map.get("invoice_code")));
row.getCell(1).setCellStyle(setBorder);
}else {
row.createCell(1).setCellValue("");
row.getCell(1).setCellStyle(setBorder);
}
//到厂日期
JSONObject jsonb = JSONObject.parseObject(String.valueOf(map.get("content")));
if(StringUtils.isNotBlank(jsonb.getString("dcdate"))) {
row.createCell(4).setCellValue(jsonb.getString("dcdate"));
row.getCell(4).setCellStyle(setBorder);
}else {
row.createCell(4).setCellValue("");
row.getCell(4).setCellStyle(setBorder);
}
//报关单号
if(map.get("entry_Id") !=null) {
row.createCell(5).setCellValue(String.valueOf(map.get("entry_Id")));
row.getCell(5).setCellStyle(setBorder);
}else {
row.createCell(5).setCellValue("");
row.getCell(5).setCellStyle(setBorder);
}
//贸易方式
if(StringUtils.isNotBlank(jsonb.getString("tradeMode_value"))) {
row.createCell(6).setCellValue(jsonb.getString("tradeMode_value"));
row.getCell(6).setCellStyle(setBorder);
}else {
row.createCell(6).setCellValue("");
row.getCell(6).setCellStyle(setBorder);
}
//申报日期
if(StringUtils.isNotBlank(jsonb.getString("declarationData"))) {
row.createCell(7).setCellValue(jsonb.getString("declarationData"));
row.getCell(7).setCellStyle(setBorder);
}else {
row.createCell(7).setCellValue("");
row.getCell(7).setCellStyle(setBorder);
}
String str = String.valueOf(map.get("json_extract"));
JSONObject json = JSONObject.parseObject(str);
if(json.containsKey("jsBLorawbNo")){
String jsBLorawbNo = json.getString("jsBLorawbNo");/**B/L OR AWB NO*/
row.createCell(2).setCellValue(jsBLorawbNo);
row.getCell(2).setCellStyle(setBorder);
}else{
row.createCell(2).setCellValue("");
row.getCell(2).setCellStyle(setBorder);
}
if(json.containsKey("jsEta")){
String jsEta = json.getString("jsEta");/**ETA*/
row.createCell(3).setCellValue(jsEta);
row.getCell(3).setCellStyle(setBorder);
}else{
row.createCell(3).setCellValue("");
row.getCell(3).setCellStyle(setBorder);
}
if(json.containsKey("jsHy")){
String jsHy = json.getString("jsHy");/**海运整柜（注明箱型）*/
row.createCell(8).setCellValue(jsHy);
row.getCell(8).setCellStyle(setBorder);
}else{
row.createCell(8).setCellValue("");
row.getCell(8).setCellStyle(setBorder);
}
if(json.containsKey("jsJzxNum")){
String jsJzxNum = json.getString("jsJzxNum");/**集装箱数量*/
row.createCell(9).setCellValue(jsJzxNum);
row.getCell(9).setCellStyle(setBorder);
}else{
row.createCell(9).setCellValue("");
row.getCell(9).setCellStyle(setBorder);
}
if(json.containsKey("jsWeight")){
String jsWeight = json.getString("jsWeight");/**毛重*/
row.createCell(10).setCellValue(jsWeight);
row.getCell(10).setCellStyle(setBorder);
}else{
row.createCell(10).setCellValue("");
row.getCell(10).setCellStyle(setBorder);
}
if(json.containsKey("jsTj")){
String jsTj = json.getString("jsTj");/**体积*/
row.createCell(11).setCellValue(jsTj);
row.getCell(11).setCellStyle(setBorder);
}else{
row.createCell(11).setCellValue("");
row.getCell(11).setCellStyle(setBorder);
}
if(json.containsKey("jsSumfy")){
String jsSumfy = json.getString("jsSumfy");/**物流费用合计金额*/
row.createCell(30).setCellValue(jsSumfy);
row.getCell(30).setCellStyle(setBorder);
}else{
row.createCell(30).setCellValue("");
row.getCell(30).setCellStyle(setBorder);
}
/**关税金额（必须和关税单金额核对）*/
String tariff = "";//关税
String addedTax = "";//增值税
double sumNum = 0;//同发票号关税和
double sumNum1 = 0;//同发票号增值税和
if(jsonb.containsKey("decLists")) {
String string = jsonb.getString("decLists");
JSONArray picArray = JSONArray.parseArray(string);
for(int q =0;q<picArray.size();q++){
JSONObject json1 = picArray.getJSONObject(q);
if(json1.get("tariff")!=null){
sumNum += Double.parseDouble(json1.getString("tariff")); //**关税金额（必须和关税单金额核对）*//*
sumNum1 += Double.parseDouble(json1.getString("addedTax"));
}
}
row.createCell(31).setCellValue(sumNum+" ");
row.getCell(31).setCellStyle(setBorder);
row.createCell(32).setCellValue(sumNum1+" ");
row.getCell(32).setCellStyle(setBorder);
}
if(json.containsKey("jsFybm")){
String jsFybm = json.getString("jsFybm");/**费用部门*/
row.createCell(33).setCellValue(jsFybm);
row.getCell(33).setCellStyle(setBorder);
}else{
row.createCell(33).setCellValue("");
row.getCell(33).setCellStyle(setBorder);
}
String sql = " select * from ldc_order_tax a where a.Invoiceno = '"+invoice_code+"' ";
List<Map<String, Object>> list1 = jdbcTemplate.queryForList(sql);
for(int j=12;j<31;j++){
row.createCell(j).setCellValue("");
}
for(int k=0;k<list1.size();k++){
Map<String, Object> map1 = new HashMap<>();
if(k<list1.size()){
map1 = list1.get(k);
}
if(map1.get("order_tax_name")!=null){
String order_tax_name = String.valueOf(map1.get("order_tax_name"));
String order_tax = String.valueOf(map1.get("order_tax"));
if(map1.get("order_tax")!=null){
order_tax = String.valueOf(map1.get("order_tax"));
}else{
order_tax = "";
}
if("报关费".equals(order_tax_name)){
row.createCell(12).setCellValue(order_tax);
}
if("换单费".equals(order_tax_name)) {
row.createCell(13).setCellValue(order_tax);
}
if("查验费".equals(order_tax_name)) {
row.createCell(14).setCellValue(order_tax);
}
if("港杂费".equals(order_tax_name)) {
row.createCell(15).setCellValue(order_tax);
}
if("THC".equals(order_tax_name)) {
row.createCell(16).setCellValue(order_tax);
}
if("LSSBAFCAF".equals(order_tax_name.trim())) {
row.createCell(17).setCellValue(order_tax);
}
if("报检费安保费".equals(order_tax_name)) {
row.createCell(18).setCellValue(order_tax);
}
if("换单代理费".equals(order_tax_name)) {
row.createCell(19).setCellValue(order_tax);
}
if("三检费".equals(order_tax_name)) {
row.createCell(20).setCellValue(order_tax);
}
if("代垫仓库理货费(港建 理货 搬移 )".equals(order_tax_name)) {
row.createCell(21).setCellValue(order_tax);
}
if("仓储费".equals(order_tax_name)) {
row.createCell(22).setCellValue(order_tax);
}
if("木托销毁".equals(order_tax_name)) {
row.createCell(23).setCellValue(order_tax);
}
if("检验代理费".equals(order_tax_name)) {
row.createCell(24).setCellValue(order_tax);
}
if("拆箱费".equals(order_tax_name)) {
row.createCell(25).setCellValue(order_tax);
}
if("运费".equals(order_tax_name)) {
row.createCell(26).setCellValue(order_tax);
}
if("坏污箱费".equals(order_tax_name)) {
row.createCell(27).setCellValue(order_tax);
}
if("集装箱超期费".equals(order_tax_name)) {
row.createCell(28).setCellValue(order_tax);
}
if("其它".equals(order_tax_name)) {
row.createCell(29).setCellValue(order_tax);
}
if("物流费".equals(order_tax_name)) {
row.createCell(30).setCellValue(order_tax);
}
// if("关税".equals(order_tax_name)) {
// row.createCell(31).setCellValue(order_tax);
// }
// if("增值税".equals(order_tax_name)) {
// row.createCell(32).setCellValue(order_tax);
// }
}
}
if(invoice_code.equals(invoice_code_old)){
cnt++;
if(i==list.size()-1){
//当最后一行时看情况合并
for(int j=0;j<34;j++){
if(j!=4&&j!=5&&j!=6&&j!=7){
row.getCell(j).setCellValue("");
CellRangeAddress region1 = new CellRangeAddress(i-cnt+2, i+2, (short) j, (short) j);
sheetAt.addMergedRegion(region1);
}
}
}
}else{
if(cnt>0){
//合并单元格
for(int j=0;j<34;j++){
if(j!=4&&j!=5&&j!=6&&j!=7){
row.getCell(j).setCellValue("");
CellRangeAddress region1 = new CellRangeAddress(i+2-cnt-1, i-1+2, (short) j, (short) j);
sheetAt.addMergedRegion(region1);
}
}
}
cnt=0;
}
invoice_code_old=invoice_code;
for(int j=12;j<31;j++){
row.getCell(j).setCellStyle(setBorder);
}
}
//合计
XSSFRow row = sheetAt.createRow(list.size()+3);
for (int i = 0; i <34; i++) {
row.createCell(i);
row.getCell(i).setCellStyle(setBorder);
}
row.createCell(0).setCellValue("合计：");
row.getCell(0).setCellStyle(setBorder);
row.getCell(8).setCellStyle(setBorder);
for(int j=12;j<33;j++){
double sum = 0;
for(int i=2;i<list.size()+2;i++){
String value = sheetAt.getRow(i).getCell(j).getStringCellValue();
if(StringUtils.isEmpty(value)){
value="0";
}
sum += Double.parseDouble(value);
}
row.createCell(j).setCellValue(sum);
row.getCell(j).setCellStyle(setBorder);
}
//前台反应
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel"); //保证不乱码
try
{
Date date=new Date();
SimpleDateFormat format=new SimpleDateFormat("MMddHHmmss");
String time="bb"+format.format(date)+".xlsx";
response.setHeader("Content-Disposition","attachment;" + " filename=" + new String(time.getBytes("utf-8"), "ISO-8859-1"));
}
catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
try
{
ByteArrayOutputStream oss =new ByteArrayOutputStream();
OutputStream os = response.getOutputStream();
work.write(oss);
byte temp[] = oss.toByteArray();
ByteArrayInputStream in1 = new ByteArrayInputStream(temp);
int n = 0;
while ((n = in1.read(temp)) >0) {
os.write(temp, 0, n);
}
os.flush();
os.close();
} catch(Exception e){
e.printStackTrace();
}
}
