这里博主为了偷懒,就没有使用mybatis以及前端页面了,纯后台代码,测试工具类是否可行
一、项目树状图
二、测试类
Cs.java - - - 主方法类
package com.export;
import java.util.List;
import java.util.Map;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import com.export.util.ExportExcelUtil;
import com.export.util.ImportExcelUtil;
import com.export.util.sqlconn;
/**
* 主方法体
* @author 48732
*
*/
public class Cs {
//下载到本地的路径,数据库sql查出的数据List<Map>
public static void main(String[] args) {
//将数据库查询集合导出为Excel文件
// dcExcel();
//将Excel数据导入数据库
drExcel();
}
/**
* 将Excel数据导入数据库
*/
public static void drExcel() {
//调用工具类
ImportExcelUtil ieu = new ImportExcelUtil();
//上传文件全路径拼接
String filePath = filePath()+"\\Students.xls";
//获取指定Excel文件中的数据
List<Map> list = ieu.getAllByExcel(filePath);
//调用dao层实体类
sqlconn cc = new sqlconn();
//向数据库中添加数据
List<Map> data = cc.sqlmain(list);
}
/**
* 将数据库查询集合导出为Excel文件
*/
public static void dcExcel() {
//调用dao层实体类,获取sql查询结果集
sqlconn cc = new sqlconn();
//数据库查询出的数据
List<Map> data = cc.sqlmain(null);
System.out.println("data = "+data);
//调用装换工具类
ExportExcelUtil eeu = new ExportExcelUtil();
//弹出弹窗获取文件本地存放路径
String filePath = filePath();
System.out.println("filePath = "+filePath);
//excel文件列名
String [] title = {"学生Id","学生姓名"};
//每列数据在数据库中对应的字段名
String [] column = {"id","name"};
//导出Excel文件名称
String fileName="Students";
//导出Excel文件
String tips = eeu.getOutputFile(title, column, data, fileName,filePath);
//弹出提示框
JOptionPane.showMessageDialog(null, tips, null, JOptionPane.PLAIN_MESSAGE);
}
/**
* 弹出选择目录框,获取选择文件夹路径
*/
public static String filePath() {
JFileChooser fileChooser = new JFileChooser();
fileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
int returnVal = fileChooser.showOpenDialog(fileChooser);
String filePath=null;
if(returnVal == JFileChooser.APPROVE_OPTION){
filePath = fileChooser.getSelectedFile().getAbsolutePath();//这个就是你选择的文件夹的路径
}
return filePath;
}
}
三、工具类
ExportExcelUtil.java - - - 导出为Excel文件
package com.export.util;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
/**
* 将sql语句查询结果集转为excel文件工具类
* @author 48732
*
*/
public class ExportExcelUtil {
private FileOutputStream fos;
private static int NUM = 10000;// 一个sheet的记录数
private static String level1 = "1";
private static String level2 = "2";
private static String level3 = "3";
private static String level4 = "4";
/**
* @param title
* 每个Sheet里的顶部大标题
* @param column
* 单个sheet里每行数据的列对应的对象属性名称
* column ="rule_name,cityName,specName,ivrName,contactGroup,specName,RulestCont".split(",");
* @param data
* 数据
* @param fileName
* 文件名
*/
public String getOutputFile(String[] title, String[] column,List<Map> data, String fileName,String filePath) {
if (title == null || title.equals("")) {
System.out.println("Excel表格 标题(表头)为空");
}
if (column == null || column.equals("")) {
System.out.println("没有定义取值字段集合");
}
if (data == null || data.equals("")) {
System.out.println("没有定义导出数据集合");
}
if (fileName == null || fileName.equals("")) {
System.out.println("没有定义输出文件名");
}
HSSFWorkbook workbook = null;
try {
fos = new FileOutputStream(filePath +"\\"+ fileName + ".xls");
workbook = new HSSFWorkbook();// 创建Excel
HSSFSheet sheet = null; // 工作表
HSSFRow row = null; // 行
HSSFCell cell = null; // 行--列
Iterator it = data.iterator();
int i = 0;
// 字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 父告警标题样式
HSSFCellStyle pStyle = workbook.createCellStyle();
pStyle.setFont(font);
// 子告警标题样式
// HSSFCellStyle sStyle = workbook.createCellStyle();
// sStyle.setFont(font);
// sStyle.setFillBackgroundColor((short) 0x3399CC);
// 告警样式
HSSFCellStyle level1Style = workbook.createCellStyle();
HSSFPalette palette = workbook.getCustomPalette();
palette.setColorAtIndex((short) 9, (byte) (0xFF), (byte) (0x00),
(byte) (0x00));
palette.setColorAtIndex((short) 10, (byte) (0xFF), (byte) (0xA5),
(byte) (0x00));
palette.setColorAtIndex((short) 11, (byte) (0xFF), (byte) (0xFF),
(byte) (0x00));
palette.setColorAtIndex((short) 12, (byte) (0x41), (byte) (0x69),
(byte) (0xE1));
level1Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
level1Style.setFillForegroundColor((short) 9);
HSSFCellStyle level2Style = workbook.createCellStyle();
level2Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
level2Style.setFillForegroundColor((short) 10);
HSSFCellStyle level3Style = workbook.createCellStyle();
level3Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
level3Style.setFillForegroundColor((short) 11);
HSSFCellStyle level4Style = workbook.createCellStyle();
level4Style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
level4Style.setFillForegroundColor((short) 12);
sheet = workbook.createSheet("Sheet0"); // 工作簿
row = sheet.createRow(0);
// 在每一页的第一行输入标题
for (int j = 0; j < title.length; j++) {
cell = row.createCell(j);
cell.setCellValue(new HSSFRichTextString(title[j]));
cell.setCellStyle(pStyle);
}
// 逐行添加数据
int k = 0;
while (it.hasNext()) {
if (i / NUM > k) { // 每50000条记录分一页
k = i / NUM;
sheet = workbook.createSheet("Sheet" + k);
row = sheet.createRow(0);
for (int j = 0; j < title.length; j++) {
cell = row.createCell(j);
cell.setCellValue(new HSSFRichTextString(title[i]));
}
}
Map dataMap = (Map) it.next();
row = sheet.createRow(i - NUM * k + 1);
// 输出数据
for (int j = 0; j < column.length; j++) {
cell = row.createCell(j);
// 按字段取值
String columnName = column[j]; //取值的key
cell.setCellValue(new HSSFRichTextString(String.valueOf(dataMap.get(columnName))));
String value = String.valueOf(dataMap.get(columnName));
if (value.equalsIgnoreCase("null") && !value.equals("0")) {
String level = String.valueOf(dataMap.get(columnName)
+ "_level");
if (!level.equalsIgnoreCase("null") && !level.equalsIgnoreCase("")) {
if (level1.equals(level)) {
cell.setCellStyle(level1Style);
} else if (level2.equals(level)) {
cell.setCellStyle(level2Style);
} else if (level3.equals(level)) {
cell.setCellStyle(level3Style);
} else if (level4.equals(level)) {
cell.setCellStyle(level4Style);
}
} else {
cell.setCellStyle(level1Style);
}
}
}
i++;
}
// 写入流
workbook.write(fos);
fos.flush();
fos.close();
return "Excel 文件导出完成";
} catch (Exception e) {
e.printStackTrace();
return "Excel导出错误!";
}
}
}
ImportExcelUtil.java - - - 将Excel文件中的数据用java代码读取出来 导入
package com.export.util;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import jxl.Sheet;
import jxl.Workbook;
public class ImportExcelUtil {
/**
* 查询指定目录中电子表格中所有的数据
* @param file 文件完整路径
* @return
*/
public static List<Map> getAllByExcel(String filePath){
Map<String, Object> map = new HashMap<String, Object>();
List<Map> list=new ArrayList<Map>();
try {
Workbook rwb=Workbook.getWorkbook(new File(filePath));
Sheet rs=rwb.getSheet(0);//表
int clos=rs.getColumns();//得到所有的列
int rows=rs.getRows();//得到所有的行
System.out.println("表的列数:"+clos+" 表的行数:"+rows);
for (int i = 1; i < rows; i++) {
for (int j = 0; j < clos; j++) {
//第一个是列数,第二个是行数
String id=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
String name=rs.getCell(j++, i).getContents();
System.out.println("id:"+id+" name:"+name);
map.put("id", id);
map.put("name", name);
list.add(new HashMap<String, Object>(map));
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
sqlconn.java - - - 连接数据库进行查询,修改添加等操作
package com.export.util;
import java.sql.*; //加入sql中本程序所需要的类,以实现链接功能
import java.text.DateFormat; //插入数据中有日期时需要用到
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class sqlconn
{
public static List<Map> sqlmain(List<Map> list) {
List<Map> map = null;
Connection conn; //与特定数据库的连接(会话)的变量
String driver = "com.mysql.jdbc.Driver"; //驱动程序名???
String url = "jdbc:mysql://127.1.1.0:3306/zx"; //指向要访问的数据库!注意后面跟的是数据库名称
String user = "root"; //navicat for sql配置的用户名
String password = "xxxxxx"; //navicat for sql配置的密码
try{
Class.forName(driver); //用class加载动态链接库——驱动程序???
conn = DriverManager.getConnection(url,user,password); //利用信息链接数据库
if(!conn.isClosed())
System.out.println("Succeeded connecting to the Database!");
if(list==null) {
map=fetchdata(conn,null); //读取数据
}else {
for(int i=0;i<list.size();i++) {
String id=String.valueOf(list.get(i).get("id"));
String name=String.valueOf(list.get(i).get("name"));
System.out.println("fetchdata(conn,id) = "+fetchdata(conn,id));
if (fetchdata(conn,id).size()!=0) {
updatedata(conn,id,name); //修改数据
}else {
insertdata(conn,id,name); //插入数据
}
}
}
// deletedata(conn); //删除数据
conn.close();
}catch(ClassNotFoundException e){ //catch不同的错误信息,并报错
System.out.println("Sorry,can`t find the Driver!");
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}finally{
System.out.println("数据库各项操作顺利进行!");
}
System.out.println("map cs2 = "+map);
return map;
}
/**
* 查询
* @param conn
*/
public static List<Map> fetchdata(Connection conn,String id) //读取数据函数
{
List<Map> map = new ArrayList<Map>();
Map<String,Object> map2 =new HashMap<String, Object>();
try
{
Statement statement = conn.createStatement(); //用statement 来执行sql语句
String sql = "select * from students"; //这是sql语句中的查询某个表,注意后面的emp是表名!!!
if (id!=null) {
System.out.println("id = = = = "+id);
sql=sql+" where id = "+id;
}
ResultSet rs = statement.executeQuery(sql); //用于返回结果
while(rs.next()){
map2.put("id", rs.getString("id"));
map2.put("name", rs.getString("name"));
map.add(new HashMap<String, Object>(map2));
}
rs.close();
}catch(SQLException e){
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}finally{
System.out.println("数据库数据读取成功!"+"\n");
}
return map;
}
public static void insertdata(Connection conn,String id,String name) //插入数据函数
{
System.out.println("添加");
try
{
PreparedStatement psql = conn.prepareStatement("insert into students (id,name) values(?,?)"); //用preparedStatement预处理来执行sql语句
psql.setInt(1, Integer.parseInt(id)); //给其五个参量分别“赋值”
psql.setString(2, name);
psql.executeUpdate(); //参数准备后执行语句
psql.close();
}catch(SQLException e){
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}finally{
System.out.println("数据库数据插入成功!"+"\n");
}
}
public static void updatedata(Connection conn,String id,String name) //修改数据函数
{
try
{
PreparedStatement psql = conn.prepareStatement("update students set name=? where id=?"); //用preparedStatement预处理来执行sql语句
psql.setString(1, name);
psql.setInt(2, Integer.parseInt(id)); //给其五个参量分别“赋值”
psql.executeUpdate(); //参数准备后执行语句
psql.close();
}catch(SQLException e){
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}finally{
System.out.println("数据库数据修改成功!"+"\n");
}
}
public static void deletedata(Connection conn) //删除数据函数
{
try
{
PreparedStatement psql; //还是用预处理
psql = conn.prepareStatement("delete from emp where sal < ?");
psql.setFloat(1, 3000.00F); //删除sal值小于3000的所有数据
psql.executeUpdate();
psql.close();
}catch(SQLException e){
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}finally{
System.out.println("数据库数据删除成功!"+"\n");
}
}
}
四、所需的jar包即pom.xml文件配置
<!-- 导入jxl -->
<!-- http://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<!-- 导入mysql -->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<!-- 导入poi -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>