POI跨行导出excell表格实现
POI跨行导出excell表格实现
需求：需要导出父子级关系的元素，父级需要跨行显示，子级需要单独显示。
由于数据库中的数据存的是子级存父级的ID形式，所以需要将数据包装，自己将装在父级下的list中，本案例将会包装成五级子级形式。
第一步：首先写sql ，sql中要计算出父级需要跨的行数。（思路：首先自身内连接，计算出每一级需要跨的行数，在与主表相连将跨行数关联上去）
<select id="findList" resultType="OcScope">
SELECT
a.* ,b.label as typeName,c.scope_name as parentName,
case when a.level =1 then '评估维度'
when a.level =2 then '评估类别'
when a.level =3 then '评估项目'
when a.level =4 then '评估内容'
when a.level =5 then '问题'
else '未知类别' end as levelName
FROM oc_scope a left join oc_dict b on a.type=b.value and b.type="evaluation_dimension"
left join oc_scope c on a.parent_id=c.id
WHERE a.status = #{DEL_FLAG_NORMAL}
<if test="scopeName != null and scopeName != ''">
AND a.scope_name LIKE
<if test="dbName == 'oracle'">'%'||#{scopeName}||'%'</if>
<if test="dbName == 'mssql'">'%'+#{scopeName}+'%'</if>
<if test="dbName == 'mysql'">CONCAT('%', #{scopeName}, '%')</if>
</if>
<if test="type != null and type != ''">
AND a.type = #{type}
</if>
<if test="level != null and level != ''">
AND a.level = #{level}
</if>
<if test="remark != null and remark != ''">
AND a.remark = #{remark}
</if>
<if test="parentId != null and parentId != ''">
AND a.parent_id = #{parentId}
</if>
<if test="scopeName != null and scopeName != ''">
AND a.scope_name LIKE CONCAT(CONCAT('%',#{scopeName},'%'))
</if>
ORDER BY a.type,a.level, a.sort
</select>
第二步：编写实体类（OcScope）：
private String type;
private String level;
private OcScope parent;
private String parentId;
private String parentName;
private String grandFatherName;
private String module;
private String typeName;
private String scopeName;
private String p1ScopeName;
private String p2ScopeName;
private String p3ScopeName;
private String p4ScopeName;
private Integer count1;
private Integer count2;
private Integer count3;
private Integer count4;
private String activityCatalogue;
private String sow;
private String activityCataloguePosition;
private String sowPosition;
private String remark;
private String sort;
private Double weights;
private Double p1Weights;
private Double p2Weights;
private Double p3Weights;
private Double p4Weights;
private Double remainWeights;
private String levelName;
private String showStatus;
.......
第三步：编写mapper层：
public List<OcScope> findCountList(OcScope ocScope);
第四步：编写server层：
一.需要先包装数据,将子级包装到list中
1.写一个实体类(ExcelDataBo)包装数据：
public class ExcelDataBo {
private String id;
private String name;
private String pid;
private Double weights;
private Integer count;
private String level;
private List<ExcelDataBo> subList;
...........
}
2.包装数据
public List<ExcelDataBo> getExcelDataList() {
List<ExcelDataBo> boList = Lists.newArrayList();
//从数据库中查询出level=1的数据
OcScope ocScope = new OcScope();
ocScope.setLevel("1");
List<OcScope> list1 = dao.findList(ocScope);
//从数据库中查询出level=2的数据
ocScope.setLevel("2");
List<OcScope> list2 = dao.findList(ocScope);
//从数据库中查询出level=3的数据
ocScope.setLevel("3");
List<OcScope> list3 = dao.findList(ocScope);
//从数据库中查询出level=4的数据
ocScope.setLevel("4");
List<OcScope> list4 = dao.findList(ocScope);
//从数据库中查询出level=5的数据
ocScope.setLevel("5");
List<OcScope> list5 = dao.findList(ocScope);
int count1=0,count2=0,count3=0,count4=0;
for (OcScope oc : list1) { // level 1
if (oc.getLevel().equals("1")) {
ExcelDataBo bo = new ExcelDataBo();
bo.setId(oc.getId());
bo.setPid(oc.getParentId());
bo.setName(oc.getScopeName());
bo.setWeights(oc.getWeights());
count1=0; //首先赋值为0，count1：用于记录该项需要跨的行数
List<ExcelDataBo> boList2 = Lists.newArrayList();
for (OcScope oc2 : list2) { // level 2
if (oc2.getParentId().equals(bo.getId())) {
ExcelDataBo bo2 = new ExcelDataBo();
bo2.setId(oc2.getId());
bo2.setPid(oc2.getParentId());
bo2.setName(oc2.getScopeName());
bo2.setWeights(oc2.getWeights());
count2=0; //首先赋值为0，count2：用于记录该项需要跨的行数
List<ExcelDataBo> boList3 = Lists.newArrayList();
for (OcScope oc3 : list3) { // level 3
if (oc3.getParentId().equals(bo2.getId())) {
ExcelDataBo bo3 = new ExcelDataBo();
bo3.setId(oc3.getId());
bo3.setPid(oc3.getParentId());
bo3.setName(oc3.getScopeName());
bo3.setWeights(oc3.getWeights());
count3=0; //首先赋值为0，count3：用于记录该项需要跨的行数
List<ExcelDataBo> boList4 = Lists.newArrayList();
for (OcScope oc4 : list4) { // level 4
if (oc4.getParentId().equals(bo3.getId())) {
ExcelDataBo bo4 = new ExcelDataBo();
bo4.setId(oc4.getId());
bo4.setPid(oc4.getParentId());
bo4.setName(oc4.getScopeName());
bo4.setWeights(oc4.getWeights());
count4=0; //首先赋值为0，count4：用于记录该项需要跨的行数
List<ExcelDataBo> boList5 = Lists.newArrayList();
for (OcScope oc5 : list5) { // level 5
if (oc5.getParentId().equals(bo4.getId())) {
ExcelDataBo bo5 = new ExcelDataBo();
bo5.setId(oc5.getId());
bo5.setPid(oc5.getParentId());
bo5.setName(oc5.getScopeName());
bo5.setWeights(oc5.getWeights());
bo5.setCount(1); //直接赋值1，这是最底层数据不存跨行的问题
boList5.add(bo5);
count4++;
}
}
if(count4==0) //有可能其下没有list子级，但是本身需要跨一行
count4=1;
bo4.setCount(count4);
bo4.setSubList(boList5);
boList4.add(bo4);
count3 = count3+count4;
}
}
if(count3==0)//有可能其下没有list子级，但是本身需要跨一行
count3=1;
bo3.setCount(count3);
bo3.setSubList(boList4);
boList3.add(bo3);
count2 = count2+count3;
}
}
if(count2==0) //有可能其下没有list子级，但是本身需要跨一行
count2=1;
bo2.setCount(count2);
bo2.setSubList(boList3);
boList2.add(bo2);
count1 = count1 + count2;
}
}
if(count1==0) //有可能其下没有list子级，但是本身需要跨一行
count1=1;
bo.setCount(count1);
bo.setSubList(boList2);
boList.add(bo);
}
}
return boList;
}
二.将数据写在excel中去
1.编写工具类（ExcelUtils)，注：该类可直接复制我的就行
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.zip.CRC32;
import java.util.zip.CheckedOutputStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;
import com.google.common.collect.Maps;
import com.thinkgem.jeesite.common.utils.IdGen;
public class ExcelUtils {
/**
* 根据路径获取Workbook
* @param filePath excel文件路径
* @return
* @throws Exception
*/
public static Workbook getWorkbook(String filePath) throws Exception{
int version = 0;
if (filePath.endsWith(".xls")) {
version = 2003;
}else if (filePath.endsWith(".xlsx")) {
version = 2007;
}else {
throw new Exception("Incorrect file format,Only allowed '.xls,.xlsx' extension");
}
Workbook workbook = null;
switch (version) {
case 2003:
POIFSFileSystem fs = new POIFSFileSystem(new BufferedInputStream(new FileInputStream(filePath)));
workbook = new HSSFWorkbook(fs);
break;
case 2007:
workbook = new XSSFWorkbook(new BufferedInputStream(new FileInputStream(filePath)));
break;
}
return workbook;
}
/**
@SuppressWarnings("unchecked")
public static void main(String[] args) throws Exception {
long start = System.currentTimeMillis();
Workbook wb = ExcelUtils.getWorkbook("D:/temp/002.xlsx");
XSSFSheet sheet = (XSSFSheet) wb.getSheet("Security");
int last = sheet.getLastRowNum();
System.out.println("rownum:"+last);
System.out.println("phy rownum:"+sheet.getPhysicalNumberOfRows());
System.out.printf("删除共花费%s毫秒\n",System.currentTimeMillis() - start);
System.out.println(sheet.getRow(1).getCell(0).getStringCellValue().equals("Level 4\n(Product)"));
Field nameField = XSSFSheet.class.getDeclaredField("_rows");
Field modifiersField = Field.class.getDeclaredField("modifiers");
modifiersField.setAccessible(true);
modifiersField.setInt(nameField, nameField.getModifiers() & ~Modifier.FINAL);//把 final从修饰集中除掉
nameField.setAccessible(true); //允许访问 private
Object object = nameField.get(sheet);
SortedMap<Integer, XSSFRow> rowMap = (SortedMap<Integer, XSSFRow>) object;
Integer index = new Integer(4);
int idx = rowMap.headMap(index).size();
Field worksheetField = XSSFSheet.class.getDeclaredField("worksheet");
worksheetField.setAccessible(true);
nameField.setAccessible(true); //允许访问 private
CTWorksheet worksheet = (CTWorksheet) worksheetField.get(sheet);
worksheet.getSheetData().removeRow(idx);
rowMap.remove(index);
System.out.println(((SortedMap<Integer, XSSFRow>)object).keySet());
List<XSSFRow> list = new ArrayList<XSSFRow>();
for (int i=0;i<last;i++) {
if (i==5) {
continue;
}
list.add(sheet.getRow(i));
}
int sheetIndex = wb.getSheetIndex(sheet);
String sheetname = sheet.getSheetName();
//wb.removeSheetAt(sheetIndex);
Sheet createSheet = wb.createSheet(sheetname+"2");
wb.setSheetOrder(sheetname+"2", sheetIndex);
for (int i=1;i<list.size()+1;i++) {
Row row = sheet.getRow(i);
XSSFRow createRow = (XSSFRow) createSheet.createRow(i);
createRow.copyRowFrom(row, new CellCopyPolicy());
}
last = sheet.getLastRowNum();
System.out.println("rownum:"+last);
start = System.currentTimeMillis();
wb.write(new FileOutputStream("D:/temp/007-test.xlsx"));
System.out.printf("写共花费%s毫秒",System.currentTimeMillis() - start);
wb.close();
}
* @throws Exception
*/
public static void testZipFiles(String filePath) throws Exception{
zipFiles("D:/temp/test/99b6d26cbea34e3890b9a16b5b2a6676","D:/temp/test/test.xlsx");
}
public static void main(String[] args) throws Exception {
String unzipExcel = unzipExcel("D:/temp/test/0010.xlsx","D:/temp/test/");
System.out.println(unzipExcel);
}
@SuppressWarnings("unchecked")
public static void editXml(String filePath) throws Exception{
long start = System.currentTimeMillis();
SAXReader reader = new SAXReader();
Document document = reader.read(new File(filePath));
Element rootElement = document.getRootElement();
Iterator<Element> elements = rootElement.element("sheetData").elementIterator("row");
int index = 0;
while (elements.hasNext()) {
Element row = elements.next();
index++;
String rownum = index+"";
if (index>2) {
//获取第一列
Element col = row.element("c");
if (col.element("v")==null) {
row.getParent().remove(row);
index--;
continue;
}
row.addAttribute("r", rownum);
List<Element> cols = row.elements("c");
for (Element element : cols) {
String colnum = element.attributeValue("r");
//数字匹配
Matcher matcher = Pattern.compile("\\d+").matcher(colnum);
matcher.find();
String colno = matcher.group();
colnum = colnum.replace(colno, "");
element.addAttribute("r", colnum + rownum);
}
}
}
/**
List<Element> elements = rootElement.element("sheetData").elements("row");
for (int i = 0; i < elements.size(); i++) {
Element row = elements.get(i);
int index = i+1;
String rownum = index+"";
if (!row.attributeValue("r").equals(rownum)) {
row.addAttribute("r", index+"");
}
List<Element> cols = row.elements("c");
for (Element element : cols) {
String colnum = element.attributeValue("r");
//数字匹配
Matcher matcher = Pattern.compile("\\d+").matcher(colnum);
matcher.find();
String colno = matcher.group();
colnum = colnum.replace(colno, "");
element.addAttribute("r", colnum + rownum);
}
}
*/
OutputFormat outputFormat = OutputFormat.createPrettyPrint();
outputFormat.setEncoding("UTF-8"); // 指定XML编码
outputFormat.setNewlines(false); //设置是否换行
outputFormat.setIndent(false); //设置是否缩进
XMLWriter writer = new XMLWriter(new FileWriter("D:/temp/0010/xl/worksheets/test.xml"), outputFormat);
writer.write(document);
writer.close();
document.clone();
System.out.println(String.format("共耗时%s ms", System.currentTimeMillis()-start));
}
@SuppressWarnings("unchecked")
public static void removeFromXml(String xmlFilePath,Map<Integer, Object> indexs) throws Exception{
long start = System.currentTimeMillis();
SAXReader reader = new SAXReader();
File xmlFile = new File(xmlFilePath);
Document document = reader.read(xmlFile);
Element rootElement = document.getRootElement();
Iterator<Element> elements = rootElement.element("sheetData").elementIterator("row");
int index = 0;
int i = 0;
for (;elements.hasNext();i++) {
Element row = elements.next();
index++;
String rownum = index+"";
if (i>=2) {
if (indexs.containsKey(i)) {
row.getParent().remove(row);
index--;
continue;
}
row.addAttribute("r", rownum);
List<Element> cols = row.elements("c");
for (Element element : cols) {
String colnum = element.attributeValue("r");
//数字匹配
Matcher matcher = Pattern.compile("\\d+").matcher(colnum);
matcher.find();
String colno = matcher.group();
colnum = colnum.replace(colno, "");
element.addAttribute("r", colnum + rownum);
}
}
}
OutputFormat outputFormat = OutputFormat.createPrettyPrint();
outputFormat.setEncoding("UTF-8"); // 指定XML编码
outputFormat.setNewlines(false); //设置是否换行
outputFormat.setIndent(false); //设置是否缩进
//创建临时文件
String tempFileName = IdGen.uuid();
File tempFile = new File(xmlFilePath.replace(xmlFile.getName(), tempFileName+".xml"));
XMLWriter writer = new XMLWriter(new FileOutputStream(tempFile), outputFormat);
writer.write(document);
writer.close();
//删除原来xml
try {
xmlFile.delete();
} catch (Exception e) {
throw new RuntimeException(e);
}
//重命名修改后的xml
tempFile.renameTo(new File(xmlFilePath));
System.out.println(String.format("共耗时%s ms", System.currentTimeMillis()-start));
}
/**
* 解压excel文件到指定临时目录,并返回解压后的临时目录
* @param excelFilePath
* @param tempPath
* @return
* @throws Exception
*/
public static String unzipExcel (String excelFilePath,String tempPath) throws Exception{
String tempDir = tempPath;
String dirName = IdGen.uuid();
ZipFile zipFile = null;
try {
zipFile = new ZipFile(new File(excelFilePath));
if (tempPath.endsWith("/")||tempPath.endsWith(File.separator)) {
tempDir = tempDir + dirName + File.separator;
}else {
tempDir = tempDir + File.separator + dirName + File.separator;
}
unZipFiles(zipFile, tempDir);
} catch (Exception e) {
throw e;
}finally{
if (zipFile!=null) {
zipFile.close();
}
}
return tempDir.replaceAll("\\\\", "/");
}
@SuppressWarnings("rawtypes")
private static void unZipFiles(ZipFile zipFile,String descDir)throws IOException{
for(Enumeration entries = zipFile.entries();entries.hasMoreElements();){
ZipEntry entry = (ZipEntry)entries.nextElement();
String zipEntryName = entry.getName();
InputStream in = zipFile.getInputStream(entry);
String outPath = (descDir+zipEntryName).replaceAll("\\\\", "/");;
//判断路径是否存在,不存在则创建文件路径
File file = new File(outPath.substring(0, outPath.lastIndexOf('/')));
if(!file.exists()){
file.mkdirs();
}
//判断文件全路径是否为文件夹,如果是上面已经上传,不需要解压
if(new File(outPath).isDirectory()){
continue;
}
//输出文件路径信息
System.out.println(outPath);
OutputStream out = new FileOutputStream(outPath);
byte[] buf1 = new byte[1024];
int len;
while((len=in.read(buf1))>0){
out.write(buf1,0,len);
}
in.close();
out.close();
}
System.out.println("******************unzip scuccess********************");
}
// public static void unZipFiles(File zipFile,String descDir)throws IOException{
// File pathFile = new File(descDir);
// if(!pathFile.exists()){
// pathFile.mkdirs();
// }
// unZipFiles(zipFile, descDir);
// }
public static void zipFiles(String inputFileName,String outPutFileName) throws IOException {
File file = new File(inputFileName);
if (!file.exists()){
throw new RuntimeException(inputFileName + "not exists！");
}
String rootPath = inputFileName;
if (inputFileName.endsWith("/")) {
rootPath = inputFileName.substring(0,inputFileName.lastIndexOf("/"));
}
FileOutputStream fileOutputStream = null;
CheckedOutputStream cos = null;
ZipOutputStream out = null;
try {
fileOutputStream = new FileOutputStream(outPutFileName);
cos = new CheckedOutputStream(fileOutputStream,new CRC32());
out = new ZipOutputStream(cos);
zipByType(file, out, "",rootPath);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally {
if (out!=null) {
out.close();
}
if (fileOutputStream!=null) {
fileOutputStream.close();
}
}
}
/**
* 判断是目录还是文件，根据类型（文件/文件夹）执行不同的压缩方法
*/
private static void zipByType(File file, ZipOutputStream out, String basedir,String rootDir) {
/* 判断是目录还是文件 */
if (file.isDirectory()) {
zipDirectory(file, out, "",rootDir);
} else {
zipFile(file, out, basedir,rootDir);
}
}
/**
* 压缩一个目录
*/
private static void zipDirectory(File dir, ZipOutputStream out, String basedir,String rootDir) {
if (!dir.exists()){
return;
}
File[] files = dir.listFiles();
for (int i = 0; i < files.length; i++) {
/* 递归 */
zipByType(files[i], out, basedir + dir.getName() + "/",rootDir);
}
}
/**
* 压缩一个文件
*/
private static void zipFile(File file, ZipOutputStream out, String basedir, String rootDir) {
if (!file.exists()) {
return;
}
try {
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
//获取文件相对于压缩文件夹根目录的子路径
String subPath = file.getAbsolutePath();
int index = subPath.indexOf(file.getAbsolutePath());
if (index != -1)
{
subPath = subPath.substring(rootDir.length() + File.separator.length());
}
ZipEntry entry = new ZipEntry(subPath);
out.putNextEntry(entry);
int count;
byte data[] = new byte[4096];
while ((count = bis.read(data, 0, 4096)) != -1) {
out.write(data, 0, count);
}
bis.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 删除Excel中需要保留Sheet之外的其他Sheet,返回Workbook对象
* @param filePath Excel文件路径
* @param sheetName 需要保留的Sheet名称
* @return 返回只包含保留Sheet的Workbook对象
* @throws Exception
*/
public static Workbook getSingleSheetWorkbook(String filePath,String sheetName) throws Exception {
Workbook workbook = getWorkbook(filePath);
int numberOfSheets = workbook.getNumberOfSheets();
String[] sheetNames = new String[numberOfSheets];
for (int i = 0; i < numberOfSheets; i++) {
sheetNames[i] = workbook.getSheetName(i);
}
for (String name : sheetNames) {
if (!sheetName.equalsIgnoreCase(name)) {
workbook.removeSheetAt(workbook.getSheetIndex(name));
}
}
return workbook;
}
/**
* 删除Excel中需要保留Sheet之外的其他Sheet,返回Workbook对象
* @param workbook Workbook对象
* @param sheets 需要保留的Sheet名称数组
* @return 返回只包含保留Sheet的Workbook对象
* @throws Exception
*/
public static Workbook getSheetWorkbook(Workbook workbook,String[] sheets) throws Exception {
int numberOfSheets = workbook.getNumberOfSheets();
String[] sheetNames = new String[numberOfSheets];
for (int i = 0; i < numberOfSheets; i++) {
sheetNames[i] = workbook.getSheetName(i);
}
Map<String, Object> sheetMap = Maps.newHashMap();
for (String sheet : sheets) {
sheetMap.put(sheet.toLowerCase(), null);
}
for (String name : sheetNames) {
if (!sheetMap.containsKey(name.toLowerCase())) {
workbook.removeSheetAt(workbook.getSheetIndex(name));
}
}
return workbook;
}
/**
* 复制单元格样式
* @param wb 新建单元格所在Workbook
* @param scrCell 源单元格,需要复制的单元格
* @param destCell 目标单元格,新建单元格
*/
public static void copyCellStyle(Workbook wb,Cell scrCell, Cell destCell) {
CellStyle newStyle = wb.createCellStyle();
copyStyle(scrCell.getCellStyle(), newStyle);
destCell.setCellStyle(newStyle);
}
// 单元格样式复制函数
private static void copyStyle(CellStyle oldStyle, CellStyle newStyle) {
newStyle.setAlignment(oldStyle.getAlignment());
//边框和边框颜色
newStyle.setBorderBottom(oldStyle.getBorderBottom());
newStyle.setBorderLeft(oldStyle.getBorderLeft());
newStyle.setBorderRight(oldStyle.getBorderRight());
newStyle.setBorderTop(oldStyle.getBorderTop());
newStyle.setTopBorderColor(oldStyle.getTopBorderColor());
newStyle.setBottomBorderColor(oldStyle.getBottomBorderColor());
newStyle.setRightBorderColor(oldStyle.getRightBorderColor());
newStyle.setLeftBorderColor(oldStyle.getLeftBorderColor());
//背景和前景
newStyle.setFillBackgroundColor(oldStyle.getFillBackgroundColor());
newStyle.setFillForegroundColor(oldStyle.getFillForegroundColor());
newStyle.setDataFormat(oldStyle.getDataFormat());
newStyle.setFillPattern(oldStyle.getFillPattern());
// newStyle.setFont(oldStyle.getFont(null));
newStyle.setHidden(oldStyle.getHidden());
newStyle.setIndention(oldStyle.getIndention());//首行缩进
newStyle.setLocked(oldStyle.getLocked());
newStyle.setRotation(oldStyle.getRotation());//旋转
newStyle.setVerticalAlignment(oldStyle.getVerticalAlignment());
newStyle.setWrapText(oldStyle.getWrapText());
}
public static void copyRowStyle(Workbook wb,Row scrRow,Row destRow){
for (int i=0;i<destRow.getLastCellNum();i++) {
Cell cell = scrRow.getCell(i);
if (cell!=null) {
CellStyle newStyle = wb.createCellStyle();
newStyle.cloneStyleFrom(scrRow.getCell(i).getCellStyle());
destRow.getCell(i).setCellStyle(newStyle);
}
}
}
/**
* 判断指定的单元格是否是合并单元格,如果是则返回合并单元格index,不是则返回-1
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
public static int isMergedRegion(Sheet sheet,int row ,int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
return i;
}
}
}
return -1;
}
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public static String getMergedRegionValue(Sheet sheet ,int row , int column){
int sheetMergeCount = sheet.getNumMergedRegions();
for(int i = 0 ; i < sheetMergeCount ; i++){
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell) ;
}
}
}
return null ;
}
/**
* 获取单元格的值
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
if (cell == null)
return "";
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return cell.getCellFormula();
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
return String.valueOf(cell.getNumericCellValue());
}
return "";
}
/**
* 合并单元格
* @param sheet
* @param firstRow 开始行
* @param lastRow 结束行
* @param firstCol 开始列
* @param lastCol 结束列
*/
public static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
}
2.将数据写到excel中去
//注入地址，防止硬编码问题
@Value("${excel.temp.dir}")
private String tempDir;
@SuppressWarnings("unchecked")
public void exportEvaluationTemplate(HttpServletRequest request, HttpServletResponse response) throws Exception {
Workbook workbook = null;
File destFile = null;
try {
//保证模板文件放在项目中，或者自己的c/d盘中，（本例是放在项目中的） 获取最新Excel模板
String classpath = exportEvaluationTemplate.class.getClassLoader().getResource("").getPath(); //获取当类的位置
String templatePath = classpath + "/templates/"; //类所在的文件夹中新建一个templates包用来存储文件的包
// templatePath：文件的地址
File srcFile = new File(templatePath+"Evaluation_Template.xlsx"); //拿到文件
String tempName = "Evaluation_Template_" + DateUtils.getDate("yyyyMMddHHmmss") + ".xlsx";
destFile = new File(tempDir + "/" + tempName);
FileUtils.copyFile(srcFile, destFile);
workbook = ExcelUtils.getWorkbook(destFile.getPath());
Sheet sheet = workbook.getSheetAt(0);
// 得到需要写入的数据
List<ExcelDataBo> rowList = getExcelDataList();
// 统计信息从第2行，第一列开始写
int rowIndex = 1; // 行
int cellIndex = 0; //列
for (ExcelDataBo bo : rowList) {
String cellName = bo.getName();
Double cellWeights = bo.getWeights();
int rowMgrCnt = bo.getCount(); // 合并行数
cellIndex = 0;
//将数据写入表格
sheet.getRow(rowIndex).getCell(cellIndex).setCellValue(cellName);
//合并所需要的行或者列
if(rowMgrCnt > 1) //如果rowMgrCnt<1,就没有必要合并单元格了
ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, cellIndex, cellIndex);
cellIndex++;
sheet.getRow(rowIndex).getCell(cellIndex).setCellValue(cellWeights);
if(rowMgrCnt > 1)
ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, cellIndex, cellIndex);
List<ExcelDataBo> boList2 = bo.getSubList();
if(boList2!=null && boList2.size()>0 ){
for (ExcelDataBo bo2 : boList2) {
rowMgrCnt = bo2.getCount(); // 合并行
int c1=cellIndex;
c1++;
sheet.getRow(rowIndex).getCell(c1).setCellValue(bo2.getName());
if(rowMgrCnt > 1)
ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c1, c1);
c1++;
// sheet.getRow(rowIndex).getCell(c1).setCellValue(bo2.getWeights());
if(bo2.getWeights()!=null && !bo2.getWeights().equals("") )
{
sheet.getRow(rowIndex).getCell(c1).setCellValue(bo2.getWeights());
}else{
sheet.getRow(rowIndex).getCell(c1).setCellValue(0);
}
if(rowMgrCnt > 1)
ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c1, c1);
List<ExcelDataBo> boList3 = bo2.getSubList();
if(boList3!=null && boList3.size()>0 ){
for (ExcelDataBo bo3 : boList3) {
rowMgrCnt = bo3.getCount(); // 合并行
int c2=c1;
c2++;
sheet.getRow(rowIndex).getCell(c2).setCellValue(bo3.getName());
if(rowMgrCnt > 1)
ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c2, c2);
c2++;
if(bo3.getWeights()!=null && !bo3.getWeights().equals("") )
{
sheet.getRow(rowIndex).getCell(c2).setCellValue(bo3.getWeights());
}else{
sheet.getRow(rowIndex).getCell(c2).setCellValue(0);
}
if(rowMgrCnt > 1)
ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c2, c2);
List<ExcelDataBo> boList4 = bo3.getSubList();
if(boList4!=null && boList4.size()>0 ){
for (ExcelDataBo bo4 : boList4) {
rowMgrCnt = bo4.getCount(); // 合并行
int c3=c2;
c3++;
sheet.getRow(rowIndex).getCell(c3).setCellValue(bo4.getName());
if(rowMgrCnt > 1)
ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c3, c3);
c3++;
if(bo4.getWeights()!=null && !bo4.getWeights().equals("") )
{
sheet.getRow(rowIndex).getCell(c3).setCellValue(bo4.getWeights());
}else{
sheet.getRow(rowIndex).getCell(c3).setCellValue(0);
}
if(rowMgrCnt > 1)
ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c3, c3);
List<ExcelDataBo> boList5 = bo4.getSubList();
if(boList5!=null && boList5.size()>0 ){
for (ExcelDataBo bo5 : boList5) {
rowMgrCnt = 1; // 合并行
int c4=c3;
c4++;
sheet.getRow(rowIndex).getCell(c4).setCellValue(bo5.getName());
if(rowMgrCnt > 1)
ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c4, c4);
c4++;
if(bo5.getWeights()!=null && !bo5.getWeights().equals("") )
{
sheet.getRow(rowIndex).getCell(c4).setCellValue(bo5.getWeights());
}else{
sheet.getRow(rowIndex).getCell(c4).setCellValue(0);
}
if(rowMgrCnt > 1)
ExcelUtils.mergeRegion(sheet, rowIndex, rowIndex + rowMgrCnt - 1, c4, c4);
//将行数怎家
rowIndex=rowIndex+rowMgrCnt;
}
}else{ //进入else就是等于说是rowMgrCnt=0，输出表格需要换行开始下一行的数据输出。
rowIndex = rowIndex+1;
}
}
}else{
rowIndex = rowIndex+1;
}
}
}else{
rowIndex = rowIndex+1;
}
}
}else{
rowIndex = rowIndex+rowMgrCnt;
}
}
OutputStream wos = null;
try {
wos = new FileOutputStream(destFile);
workbook.write(wos);
} catch (Exception e) {
throw e;
} finally {
if (wos != null) {
wos.close();
}
}
FileUtils.downFile(destFile, request, response);
} catch (Exception e) {
throw e;
} finally {
if (workbook != null) {
workbook.close();
}
if (destFile != null) {
try {
destFile.delete();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
}
第五步：编写controller层：
/**
* 表单导出方法
* @param request
* @param response
* @throws Exception
*/
@RequestMapping(value = "export")
public void exportEvaluationTemplate(HttpServletRequest request, HttpServletResponse response) throws Exception {
ocScopeService.exportEvaluationTemplate(request,response);
}
