使用SmbFile导出excel到共享文件目录

来源：互联网发布：js 对象复制互不影响编辑：程序博客网时间：2024/06/07 12:43

pom.xml：

<dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.8</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>3.8</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.11-beta2</version></dependency><dependency>    <groupId>jcifs</groupId>    <artifactId>jcifs</artifactId>    <version>1.3.17</version></dependency>

import java.io.IOException;import java.net.MalformedURLException;import java.net.UnknownHostException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import jcifs.smb.SmbException;import jcifs.smb.SmbFile;import jcifs.smb.SmbFileInputStream;import jcifs.smb.SmbFileOutputStream;/*** * excel分次对sheet写入数据 * @author dunjie * */public class BatchWriteExcel{    //远程共享文件夹路径    private String basePath;    //远程共享文件模板，空文件且可以正常打开，只有一个sheet(名称:Sheet1)    private String tempFile ;    //模板文件    private SmbFile tempSmb;    //复制的文件，即输出excel文件    private SmbFile copySmb;    public BatchWriteExcel(String smbPassword,String smbDir){        basePath = "smb://" + smbPassword+ "@" + smbDir + "/";        tempFile = basePath+"template.xlsx";    }    /***     * 生成excel     * @param fileName 文件名（不需要后缀）     * @param userName 文件夹名，即用户名称     * @throws IOException     */    public SmbFile createExcel(String fileName,String userName) throws IOException{        tempSmb = new SmbFile(tempFile);        SmbFile userDir = new SmbFile(basePath+userName);        if(!userDir.exists()){            userDir.mkdir();        }        String excelFilePath = basePath+userName+"/"+fileName+".xlsx";        copySmb = new SmbFile(excelFilePath);        tempSmb.copyTo(copySmb);        return copySmb;    }    /***     * 写入excel数据     * #1、创建sheet     * #2、创建Row列并设置表头的列     * #3、写入数据     * @param copySmb excel文件     * @param sheetName sheet名称     * @param columnValues Row表头的所有标题 "name=名称,age=年龄"     * @param list<map> 数据 [{name:名称,age:年龄},..]     */    public void writeRows(SmbFile copySmb,String sheetName,String columnValues,List<Map<String, String>> map) {        SmbFileInputStream inStream = null;        SmbFileOutputStream outStream = null;        XSSFWorkbook wb = null;        try {            inStream = new SmbFileInputStream(copySmb);            wb = new XSSFWorkbook(inStream);            //把第一个sheet删掉(模板文件必须有一个sheet，只有一个sheet时手动无法删除)            if(wb.getSheet("Sheet1")!=null){                wb.removeSheetAt(0);            }            Sheet sheet = wb.createSheet(sheetName);            Row row = sheet.createRow(0);            String[] columns = columnValues.split(",");            List<Map<String,String>> tempList = new ArrayList<Map<String,String>>();            for (int i = 0; i < columns.length; i++) {                String[] cs = columns[i].split("=");                Map<String,String> m = new HashMap<String,String>();                m.put("field", cs[0]);                m.put("title", cs[1]);                tempList.add(m);            }            int cellTotal = columns.length;            Cell[] cell = new Cell[cellTotal];            for (int i = 0; i < cell.length; i++) {                cell[i] = row.createCell(i);            }            for (int i = 0; i < tempList.size(); i++) {                cell[i].setCellValue(tempList.get(i).get("title"));            }            CellStyle linkStyle = wb.createCellStyle();            Font cellFont = wb.createFont();            cellFont.setUnderline((byte) 1);            cellFont.setColor(HSSFColor.BLUE.index);            linkStyle.setFont(cellFont);            for (int i = 0; i < map.size(); i++) {                Map<String, String> m = map.get(i);                Row row2 = sheet.createRow(i + 1);                Cell[] cell2 = new Cell[cellTotal];                for (int cellnum = 0; cellnum < cell2.length; cellnum++) {                    cell2[cellnum] = row2.createCell(cellnum);                }                for (int j = 0; j < tempList.size(); j++) {                    String cellValue = "";                    if(tempList.get(j).get("field")!=null && m.get(tempList.get(j).get("field"))!=null){                        cellValue =  String.valueOf(m.get(tempList.get(j).get("field")));                    }                    cell2[j].setCellValue(cellValue);                }            }            outStream  = new SmbFileOutputStream(copySmb);            wb.write(outStream);        } catch (SmbException | MalformedURLException | UnknownHostException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }        finally{            try {                outStream.flush();                outStream.close();            } catch (IOException e) {                e.printStackTrace();            }           }    }    public void closeSmbFile(){        if(tempSmb!=null){            tempSmb = null;        }        if(copySmb!=null){            copySmb = null;        }    }}

说明：需要准备一个空的模板文件template.xlsx用于每次生成excel的复制，再写入数据
如何调用：

BatchWriteExcel bwe = new BatchWriteExcel("Administrator:password","共享目录的网络路径");SmbFile smb = bwe.createExcel("excel名称","需保存到共享目录下的文件夹名称");//该方法可循环调用，每次调用会在同一个excel生成一个新的sheet页bwe.writeRows(smb, "sheet名称", "name=名称,age=年龄", null);bwe.closeSmbFile();

0 0