java excel打印_技巧（excel，excel使用java打印输出）

1、=TEXT(A2,"emmdd")&TEXT(COUNTIF(A$2:A2,A2),"000")

去掉日历中的"-"

2、使用视图-》冻结窗格可以锁定行列

1、java.io.FileNotFoundException: D:\xxx\yyy (拒绝访问。)

FileOutputStream fout = new FileOutputStream("D:\\greatmicro\\"+filename);在输出文件的时候没有加上具体的文件名，添上去即可

excel导入

excel导出

导入导出

输出异同的excel

package com.greatmicro.demo.test;

import java.io.*;

import java.util.ArrayList;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import java.util.Map.Entry;

import org.apache.commons.lang.StringUtils;

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.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.DateUtil;

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 javax.servlet.ServletContext;

import javax.servlet.http.HttpServletResponse;

public class ExcelDiff {

public static void main(String[] args) throws IOException {

//取出代码部分的字段

List> maps = returnList("C:\\Users\\Administrator\\Desktop\\seaport.xlsx");

List> maps2 = returnHardList("C:\\Users\\Administrator\\Desktop\\起运港-目的港表(不区分内陆港).xlsx");

// Map stringStringMap = maps2.get(1);

// for (Map map : maps){

// System.out.println(map.get("name"));

// }

// 放在map中进行去重

List> sameList = new ArrayList>();

List> seaportList = new ArrayList>();

List> loadingPortList = maps2;

for (Map map : maps){

if(maps2.contains(map)){

sameList.add(map);

loadingPortList.remove(map);

} else {

seaportList.add(map);

}

}

for (Map map : sameList){

System.out.println("两个都有的：" + map.get("name"));

}

for (Map map : seaportList){

System.out.println("seaport特有：" + map.get("name"));

}

for (Map map : loadingPortList){

System.out.println("loadingPort特有：" + map.get("name"));

}

try {

excelOut(sameList,seaportList,loadingPortList);

} catch (IOException e) {

e.printStackTrace();

}

// seaport特有的

// for (Map map : maps){

// if(maps2.contains(map)){

// continue;

// } else{

// seaportList.add(map);

// }

// }

// 起运港特有的

// for (Map map : maps){

// loadingPortList.remove()

// if(maps2.contains(map)){

// continue;

// } else{

// seaportList.add(map);

// }

// }

}

private static List> returnList(String path) {

Workbook wb =null;

Sheet sheet = null;

Row row = null;

List> list = null;

String cellData = null;

String filePath = path;

String columns[] = {"name"};

wb = readExcel(filePath);

if(wb != null){

//用来存放表中数据

list = new ArrayList>();

//获取第一个sheet

sheet = wb.getSheetAt(0);

//获取最大行数

int rownum = sheet.getPhysicalNumberOfRows();

//获取第一行

row = sheet.getRow(0);

//获取最大列数

int colnum = row.getPhysicalNumberOfCells();

System.out.println(rownum);

for (int i = 0; i

Map map = new LinkedHashMap();

row = sheet.getRow(i);

if(row !=null){

for (int j=0;j<1;j++){

if(StringUtils.isNotEmpty(String.valueOf(row.getCell(1)))) {

cellData = (String) getCellFormatValue(row.getCell(j));

map.put(columns[j], cellData);

}

}

}else{

break;

}

list.add(map);

}

}

//遍历解析出来的list

// for (Map map : list) {

// for (Entry entry : map.entrySet()) {

// System.out.print(entry.getKey()+":"+entry.getValue()+",");

// }

// System.out.println();

// }

return list;

}

private static List> returnHardList(String path) {

Workbook wb =null;

Sheet sheet = null;

Row row = null;

List> list = null;

String cellData = null;

String filePath = path;

String columns[] = {"name"};

wb = readExcel(filePath);

if(wb != null){

//用来存放表中数据

list = new ArrayList>();

//获取第一个sheet

sheet = wb.getSheetAt(0);

//获取最大行数

int rownum = sheet.getPhysicalNumberOfRows();

//获取第一行

row = sheet.getRow(0);

//获取最大列数

int colnum = row.getPhysicalNumberOfCells();

for (int i = 0; i

Map map = new LinkedHashMap();

row = sheet.getRow(i);

if(row !=null){

if(StringUtils.isNotEmpty(String.valueOf(row.getCell(1)))){

cellData = (String) getCellFormatValue(row.getCell(1));

map.put(columns[0], cellData);

}

}else{

break;

}

list.add(map);

}

for (int i = 0; i

Map map = new LinkedHashMap();

row = sheet.getRow(i);

if(row !=null){

if(StringUtils.isNotEmpty(String.valueOf(row.getCell(8)))){

cellData = (String) getCellFormatValue(row.getCell(8));

map.put(columns[0], cellData);

}

}else{

break;

}

list.add(map);

}

}

//遍历解析出来的list

// for (Map map : list) {

// for (Entry entry : map.entrySet()) {

// System.out.print(entry.getKey()+":"+entry.getValue()+",");

// }

// System.out.println();

// }

return list;

}

public static void excelOut(List> mapList,List> mapList2,List> mapList3) throws IOException {

//使用poi下载文件

HSSFWorkbook workbook = new HSSFWorkbook();

//创建sheet

HSSFSheet sheet1 = workbook.createSheet("分区信息一");

//创建row信息

HSSFRow row = sheet1.createRow(0);

//创建单元格头标

row.createCell(0).setCellValue("共有");

row.createCell(1).setCellValue("seaport特有");

row.createCell(2).setCellValue("loadingPort特有");

//获取数据

if (mapList != null) {

for (Map e : mapList) {

int lastRowNum = sheet1.getLastRowNum();

HSSFRow lastRow = sheet1.createRow(lastRowNum + 1);

lastRow.createCell(0).setCellValue(e.get("name"));

}

}

if (mapList2 != null) {

for (Map e : mapList2) {

int lastRowNum = sheet1.getLastRowNum();

HSSFRow lastRow = sheet1.createRow(lastRowNum + 1);

lastRow.createCell(1).setCellValue(e.get("name"));

}

}

if (mapList3 != null) {

for (Map e : mapList3) {

int lastRowNum = sheet1.getLastRowNum();

HSSFRow lastRow = sheet1.createRow(lastRowNum + 1);

lastRow.createCell(2).setCellValue(e.get("name"));

}

}

String filename = "重复代码信息.xls";

//设置文件输出头

//设置文件类型servletAction.getMine

//下载输出流

// workbook.write(System.out.);

FileOutputStream fout = new FileOutputStream("D:\\greatmicro\\"+filename);

try {

workbook.write(fout);

fout.close();

} catch (IOException e) {

e.printStackTrace();

}

}

//读取excel

public static Workbook readExcel(String filePath){

Workbook wb = null;

if(filePath==null){

return null;

}

String extString = filePath.substring(filePath.lastIndexOf("."));

InputStream is = null;

try {

is = new FileInputStream(filePath);

if(".xls".equals(extString)){

return wb = new HSSFWorkbook(is);

}else if(".xlsx".equals(extString)){

return wb = new XSSFWorkbook(is);

}else{

return wb = null;

}

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

return wb;

}

public static Object getCellFormatValue(Cell cell){

Object cellValue = null;

if(cell!=null){

//判断cell类型

switch(cell.getCellType()){

case Cell.CELL_TYPE_NUMERIC:{

cellValue = String.valueOf(cell.getNumericCellValue());

break;

}

case Cell.CELL_TYPE_FORMULA:{

//判断cell是否为日期格式

if(DateUtil.isCellDateFormatted(cell)){

//转换为日期格式YYYY-mm-dd

cellValue = cell.getDateCellValue();

}else{

//数字

cellValue = String.valueOf(cell.getNumericCellValue());

}

break;

}

case Cell.CELL_TYPE_STRING:{

cellValue = cell.getRichStringCellValue().getString();

break;

}

default:

cellValue = "";

}

}else{

cellValue = "";

}

return cellValue;

}

}