1.基本介绍
excel的导出导入，使用了组件jxls；csv的导出导入，使用了组件opencsv。组件是挺好用，可惜官网没有找到完整的例子，尤其是针对日期格式的处理，相关例子更少。经过一番研究，对一些基本的功能给出示例代码，对于更复杂的功能就请参看官方文档了。
需要加入的jar包：
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.7</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.16</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>2.0.5</version>
</dependency>
<dependency>
<groupId>com.opencsv</groupId>
<artifactId>opencsv</artifactId>
<version>4.0</version>
</dependency>
操作的目标bean
public class LogResult {
private int logId;
private String model;
private String ip;
private String operatorName;
private String logContent;
private Date gmtCreate;
public static List<LogResult> generateSampleData() throws ParseException {
List<LogResult> logReports = new ArrayList<LogResult>();
logReports.add( new LogResult(1, "1", "192.168.1.1", "张三1", "内容1", new Date()) );
logReports.add( new LogResult(2, "2", "192.168.1.1", "张三1", "内容2", new Date()) );
logReports.add( new LogResult(3, "3", "192.168.1.1", "张三1", "内容3", new Date()) );
logReports.add( new LogResult(4, "4", "192.168.1.2", "张三2", "内容4", new Date()) );
logReports.add( new LogResult(5, "5", "192.168.1.2", "张三2", "内容5", new Date()) );
logReports.add( new LogResult(6, "6", "192.168.1.3", "张三3", "内容6", new Date()) );
logReports.add( new LogResult(7, "7", "192.168.1.3", "张三3", "内容7", new Date()) );
return logReports;
}
}
2.excel导出：
-
首先需要设置导出模板，也就是告诉程序，bean对象和xls文件是如何对应的。
导出模板
模板说明：
单元格A1添加注释，表示模板范围为A1~F2
jx:area(lastCell="F2")
单元格A2添加注释，表示模板与bean的映射关系
jx:each(items="logReports" var="logReport" lastCell="F2")
对于日期的处理，dateFormat是一个自定义对象
${dateFormat.format(logReport.gmtCreate)}
- 导出
public class MyExport {
public static void main(String[] args) throws IOException, ParseException {
List<LogResult> logReports = LogResult.generateSampleData();
try (InputStream is = LogResult.class.getResourceAsStream("myExport.xls")) {
try (OutputStream os = new FileOutputStream("target/myExport.xls")) {
//设置待导出数据
Context context = new Context();
context.putVar("logReports", logReports);
//日期格式化
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
context.putVar("dateFormat", dateFormat);
JxlsHelper.getInstance().processTemplate(is, os, context);
}
}
}
}
因为使用了组件，所以导出变得十分简单，需要注意的是对日期格式要进行额外处理，这里自定义了一个dateFormat对象。
3.csv导出：
csv的导出不需要设置模板，表头和字段都是自己手动控制的，同样也需要注意对日期格式的处理。
public class MyExport {
//设置头
private static final String[] CSV_HEADER = { "日志id", "模块", "ip", "操作员", "日志内容", "操作时间" };
public static void main(String[] args) throws ParseException {
//待导出数据
List<LogResult> logReports = LogResult.generateSampleData();
FileWriter fileWriter = null;
CSVWriter csvWriter = null;
try {
fileWriter = new FileWriter("myExport.csv");
//设置输出格式
csvWriter = new CSVWriter(fileWriter, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER,
CSVWriter.DEFAULT_ESCAPE_CHARACTER, CSVWriter.DEFAULT_LINE_END);
//输出头
csvWriter.writeNext(CSV_HEADER);
//日期转换
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
//输出对象
for (LogResult logReport : logReports) {
String[] data = { logReport.getLogId()+"", logReport.getModel(), logReport.getIp(), logReport.getOperatorName(), logReport.getLogContent(),dateFormat.format(logReport.getGmtCreate()) };
csvWriter.writeNext(data);
}
System.out.println("Write CSV using CSVWriter successfully!");
} catch (Exception e) {
System.out.println("Writing CSV error!");
e.printStackTrace();
} finally {
try {
fileWriter.flush();
csvWriter.close();
fileWriter.close();
} catch (IOException e) {
System.out.println("Flushing/closing error!");
e.printStackTrace();
}
}
}
}
4.excel导入：
- 首先需要配置模板，向程序说明excel文件和bean的映射关系，这次是使用xml文件。关于xml怎么写，就结合xls和xml文件来自己分析吧，用文字解释反而很麻烦。
<workbook>
<worksheet name="Sheet1"><!-- 这里是sheet -->
<section startRow="0" endRow="0" /><!-- 这里是表头 -->
<loop startRow="1" endRow="1" items="result" var="logReport" varType="myImport.xlsx.LogResult"><!-- 这里是表内容 -->
<section startRow="1" endRow="1">
<mapping row="1" col="0">logReport.logId</mapping>
<mapping row="1" col="1">logReport.model</mapping>
<mapping row="1" col="2">logReport.ip</mapping>
<mapping row="1" col="3">logReport.operatorName</mapping>
<mapping row="1" col="4">logReport.logContent</mapping>
<mapping row="1" col="5">logReport.gmtCreate</mapping>
</section>
<loopbreakcondition><!-- 循环结束条件：0行0列为空值时结束 -->
<rowcheck offset="0">
<cellcheck offset="0"/>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>
</workbook>
-
待导入的文件
待导入的文件 导入
public class MyImport {
public static void main(String[] args) throws Exception {
List<LogResult> logReports = parseExcelFileToBeans(new File("D:\\myImport.xls"),
new File("D:\\config.xml"));
System.out.println(logReports);
}
public static <T> List<T> parseExcelFileToBeans(final File xlsFile, final File jxlsConfigFile) throws Exception {
final XLSReader xlsReader = ReaderBuilder.buildFromXML(jxlsConfigFile);
final List<T> result = new ArrayList<>();
final Map<String, Object> beans = new HashMap<>();
beans.put("result", result);
try (InputStream inputStream = new BufferedInputStream(new FileInputStream(xlsFile))) {
xlsReader.read(inputStream, beans);
}
return result;
}
}
注意myImport.xls文件中的gmtCreate列，需要按日期格式填写，只要excel能将其识别为日期，jxls就能将其映射成java.util.Date类型。如果你填入一个看起来像日期的字符串，那就肯定映射不上去了。
5.csv导入：
- 待导入的csv文件
日志id,模块,ip,操作员,日志内容,操作时间
1,1,192.168.1.1,张三1,内容1,2018-11-29 10:49:48
2,2,192.168.1.1,张三1,内容2,2018-11-29 10:49:48
3,3,192.168.1.1,张三1,内容3,2018-11-29 10:49:48
4,4,192.168.1.2,张三2,内容4,2018-11-29 10:49:48
5,5,192.168.1.2,张三2,内容5,2018-11-29 10:49:48
6,6,192.168.1.3,张三3,内容6,2018-11-29 10:49:48
7,7,192.168.1.3,张三3,内容7,2018-11-29 10:49:48
- 使用opencsv导入，这里是按位置进行映射,
public class MyImport {
private static final String SAMPLE_CSV_FILE_PATH = "D:\\with-header.csv";
public static void main(String[] args) throws IOException {
//处理日期格式
PropertyEditorManager.registerEditor(Date.class, DateEditor.class);
try (
Reader reader = Files.newBufferedReader(Paths.get(SAMPLE_CSV_FILE_PATH));
) {
//按位置进行映射
ColumnPositionMappingStrategy<LogResult> strategy = new ColumnPositionMappingStrategy<LogResult>();
strategy.setType(LogResult.class);
String[] memberFieldsToBindTo = {"logId", "model", "ip", "operatorName", "logContent", "gmtCreate"};
strategy.setColumnMapping(memberFieldsToBindTo);
CsvToBean<LogResult> csvToBean = new CsvToBeanBuilder<LogResult>(reader)
.withMappingStrategy(strategy)
.withSkipLines(1)
.withIgnoreLeadingWhiteSpace(true)
.build();
Iterator<LogResult> LogResultIterator = csvToBean.iterator();
while (LogResultIterator.hasNext()) {
LogResult LogResult = LogResultIterator.next();
System.out.println(LogResult);
}
}
}
}
- 注意这里的日期处理，需要额外定义一个类
public class DateEditor extends PropertyEditorSupport {
public static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**
* 处理日期格式
*/
@Override
public void setAsText(String text){
try {
setValue(sdf.parse(text));
} catch (ParseException e) {
e.printStackTrace();
}
}
}
