导入和导出Excel通用方法
Java导出Excel通用方法,只需要一个list 集合。通用方法改进之处踊跃提出
[java] view
plain copy
- package oa.common.utils;
- import java.io.OutputStream;
- import java.util.List;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.struts2.ServletActionContext;
- import java.lang.reflect.Field;
- import jxl.Workbook;
- import jxl.format.Alignment;
- import jxl.format.Border;
- import jxl.format.BorderLineStyle;
- import jxl.format.VerticalAlignment;
- import jxl.write.Label;
- import jxl.write.WritableCellFormat;
- import jxl.write.WritableFont;
- import jxl.write.WritableSheet;
- import jxl.write.WritableWorkbook;
- /***
- * @author lsf
- */
- public class ExportExcel {
- /***************************************************************************
- * @param fileName EXCEL文件名称
- * @param listTitle EXCEL文件第一行列标题集合
- * @param listContent EXCEL文件正文数据集合
- * @return
- */
- public final static String exportExcel(String fileName,String[] Title, List<Object> listContent) {
- String result="系统提示:Excel文件导出成功!";
- // 以下开始输出到EXCEL
- try {
- //定义输出流,以便打开保存对话框______________________begin
- HttpServletResponse response=ServletActionContext.getResponse();
- OutputStream os = response.getOutputStream();// 取得输出流
- response.reset();// 清空输出流
- response.setHeader("Content-disposition", "attachment; filename="+ new String(fileName.getBytes("GB2312"),"ISO8859-1"));
- // 设定输出文件头
- response.setContentType("application/msexcel");// 定义输出类型
- //定义输出流,以便打开保存对话框_______________________end
- /** **********创建工作簿************ */
- WritableWorkbook workbook = Workbook.createWorkbook(os);
- /** **********创建工作表************ */
- WritableSheet sheet = workbook.createSheet("Sheet1", 0);
- /** **********设置纵横打印(默认为纵打)、打印纸***************** */
- jxl.SheetSettings sheetset = sheet.getSettings();
- sheetset.setProtected(false);
- /** ************设置单元格字体************** */
- WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
- WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);
- /** ************以下设置三种单元格样式,灵活备用************ */
- // 用于标题居中
- WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
- wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
- wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
- wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
- wcf_center.setWrap(false); // 文字是否换行
- // 用于正文居左
- WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
- wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
- wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
- wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
- wcf_left.setWrap(false); // 文字是否换行
- /** ***************以下是EXCEL开头大标题,暂时省略********************* */
- //sheet.mergeCells(0, 0, colWidth, 0);
- //sheet.addCell(new Label(0, 0, "XX报表", wcf_center));
- /** ***************以下是EXCEL第一行列标题********************* */
- for (int i = 0; i < Title.length; i++) {
- sheet.addCell(new Label(i, 0,Title[i],wcf_center));
- }
- /** ***************以下是EXCEL正文数据********************* */
- Field[] fields=null;
- int i=1;
- for(Object obj:listContent){
- fields=obj.getClass().getDeclaredFields();
- int j=0;
- for(Field v:fields){
- v.setAccessible(true);
- Object va=v.get(obj);
- if(va==null){
- va="";
- }
- sheet.addCell(new Label(j, i,va.toString(),wcf_left));
- j++;
- }
- i++;
- }
- /** **********将以上缓存中的内容写到EXCEL文件中******** */
- workbook.write();
- /** *********关闭文件************* */
- workbook.close();
- } catch (Exception e) {
- result="系统提示:Excel文件导出失败,原因:"+ e.toString();
- System.out.println(result);
- e.printStackTrace();
- }
- return result;
- }
- }
测试:
[java] view
plain copy
- /**
- * 导出excel
- * @return
- */
- public String excelPage(){
- ExportExcel excel=new ExportExcel();
- String str="";
- try {
- str = new String(getHTTP.getRequest().getParameter("wineOrg.orgName").getBytes("iso8859-1"),"UTF-8");
- } catch (UnsupportedEncodingException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- wineOrg.setOrgName(str);
- List<Object> li=service.exportExcel(wineOrg);
- String[] Title={"机构ID","会员编号","类别","名称","省ID","省名称","城市ID","城市名称","详细地址","联系人","性别","联系手机","联系电话","传真","邮箱","QQ","生日","积分","客户等级","现金账户余额","结算方式","客户类型","购买次数","购买支数","创建人ID","创建人姓名","create_time","del","STS","备注","负责人ID","负责人姓名","审核标识","审核人ID ","审核人姓名","审核日期","分配人ID","分配人姓名","分配日期","修改人ID","修改人姓名 ","修改时间"};
- excel.exportExcel("客户资料信息.xls",Title, li);
- return SUCCESS;
- }
Java导入Excel通用方法
实现的功能:
- Java实现Excel导入数据库,如果存在就更新
- 数据库中的数据导入到Excel
1、添加jxl.jar mysql-connector-java.1.7-bin.jar包到项目的lib目录下
2、Excel文件目录:D://book.xls
3、数据库名:javenforexcel
4、表名:stu
5、编写类:连接mysql的字符串方法、插入的方法、实体类
表结构如下 :
连接数据库的工具类
package com.javen.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBhepler {
/*String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://127.0.0.1;DatabaseName=javenforexcel";*/
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/javenforexcel";
Connection con = null;
ResultSet res = null;
public void DataBase() {
try {
Class.forName(driver);
con = DriverManager.getConnection(url, "root", "root");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.err.println("装载 JDBC/ODBC 驱动程序失败。" );
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.err.println("无法连接数据库" );
e.printStackTrace();
}
}
// 查询
public ResultSet Search(String sql, String str[]) {
DataBase();
try {
PreparedStatement pst =con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i < str.length; i++) {
pst.setString(i + 1, str[i]);
}
}
res = pst.executeQuery();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return res;
}
// 增删修改
public int AddU(String sql, String str[]) {
int a = 0;
DataBase();
try {
PreparedStatement pst = con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i < str.length; i++) {
pst.setString(i + 1, str[i]);
}
}
a = pst.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return a;
}
}
表的实体如下
package com.javen.entity;
/**
* @author Javen
* @Email [email protected]
*
*/
public class StuEntity {
private int id;
private String name;
private String sex;
private int num;
public StuEntity() {
}
public StuEntity(int id, String name, String sex, int num) {
this.id = id;
this.name = name;
this.sex = sex;
this.num = num;
}
@Override
public String toString() {
return "StuEntity [id=" + id + ", name=" + name + ", sex=" + sex
+ ", num=" + num + "]";
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
}
Java实现Excel导入数据核心类 读取Excel表中所有的数据、操作数据(查询、更新)
package com.javen.service;
import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import com.javen.db.DBhepler;
import com.javen.entity.StuEntity;
/**
* @author Javen
* @Email [email protected]
*
*/
public class StuService {
/**
* 查询stu表中所有的数据
* @return
*/
public static List<StuEntity> getAllByDb(){
List<StuEntity> list=new ArrayList<StuEntity>();
try {
DBhepler db=new DBhepler();
String sql="select * from stu";
ResultSet rs= db.Search(sql, null);
while (rs.next()) {
int id=rs.getInt("id");
String name=rs.getString("name");
String sex=rs.getString("sex");
int num=rs.getInt("num");
//System.out.println(id+" "+name+" "+sex+ " "+num);
list.add(new StuEntity(id, name, sex, num));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
/**
* 查询指定目录中电子表格中所有的数据
* @param file 文件完整路径
* @return
*/
public static List<StuEntity> getAllByExcel(String file){
List<StuEntity> list=new ArrayList<StuEntity>();
try {
Workbook rwb=Workbook.getWorkbook(new File(file));
Sheet rs=rwb.getSheet("Test Shee 1");//或者rwb.getSheet(0)
int clos=rs.getColumns();//得到所有的列
int rows=rs.getRows();//得到所有的行
System.out.println(clos+" rows:"+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();
String sex=rs.getCell(j++, i).getContents();
String num=rs.getCell(j++, i).getContents();
System.out.println("id:"+id+" name:"+name+" sex:"+sex+" num:"+num);
list.add(new StuEntity(Integer.parseInt(id), name, sex, Integer.parseInt(num)));
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
/**
* 通过Id判断是否存在
* @param id
* @return
*/
public static boolean isExist(int id){
try {
DBhepler db=new DBhepler();
ResultSet rs=db.Search("select * from stu where id=?", new String[]{id+""});
if (rs.next()) {
return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
public static void main(String[] args) {
/*List<StuEntity> all=getAllByDb();
for (StuEntity stuEntity : all) {
System.out.println(stuEntity.toString());
}*/
System.out.println(isExist(1));
}
}
数据的数据导入到Excel表
package com.javen.excel;
import java.io.File;
import java.util.List;
import com.javen.entity.StuEntity;
import com.javen.service.StuService;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class TestDbToExcel {
public static void main(String[] args) {
try {
WritableWorkbook wwb = null;
// 创建可写入的Excel工作簿
String fileName = "D://book.xls";
File file=new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
//以fileName为文件名来创建一个Workbook
wwb = Workbook.createWorkbook(file);
// 创建工作表
WritableSheet ws = wwb.createSheet("Test Shee 1", 0);
//查询数据库中所有的数据
List<StuEntity> list= StuService.getAllByDb();
//要插入到的Excel表格的行号,默认从0开始
Label labelId= new Label(0, 0, "编号(id)");//表示第
Label labelName= new Label(1, 0, "姓名(name)");
Label labelSex= new Label(2, 0, "性别(sex)");
Label labelNum= new Label(3, 0, "薪水(num)");
ws.addCell(labelId);
ws.addCell(labelName);
ws.addCell(labelSex);
ws.addCell(labelNum);
for (int i = 0; i < list.size(); i++) {
Label labelId_i= new Label(0, i+1, list.get(i).getId()+"");
Label labelName_i= new Label(1, i+1, list.get(i).getName());
Label labelSex_i= new Label(2, i+1, list.get(i).getSex());
Label labelNum_i= new Label(3, i+1, list.get(i).getNum()+"");
ws.addCell(labelId_i);
ws.addCell(labelName_i);
ws.addCell(labelSex_i);
ws.addCell(labelNum_i);
}
//写进文档
wwb.write();
// 关闭Excel工作簿对象
wwb.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Excel表中的数据导入到MySql数据库
package com.javen.excel;
import java.util.List;
import com.javen.db.DBhepler;
import com.javen.entity.StuEntity;
import com.javen.service.StuService;
/**
* @author Javen
* @Email [email protected]
*
*/
public class TestExcelToDb {
public static void main(String[] args) {
//得到表格中所有的数据
List<StuEntity> listExcel=StuService.getAllByExcel("d://book.xls");
/*//得到数据库表中所有的数据
List<StuEntity> listDb=StuService.getAllByDb();*/
DBhepler db=new DBhepler();
for (StuEntity stuEntity : listExcel) {
int id=stuEntity.getId();
if (!StuService.isExist(id)) {
//不存在就添加
String sql="insert into stu (name,sex,num) values(?,?,?)";
String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""};
db.AddU(sql, str);
}else {
//存在就更新
String sql="update stu set name=?,sex=?,num=? where id=?";
String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+"",id+""};
db.AddU(sql, str);
}
}
}
}
如果以上代码对你有帮助请留下脚印 支持一下 转载请标明地址 欢迎吐槽
源代码下载地址 http://download.****.net/detail/zyw_java/7430807
相关推荐
- MongoDB学习笔记(三)--权限 && 导出导入备份恢复 && fsync和锁
- 导入和导出Excel通用方法
- Java之——导出Excel通用工具类
- 导出Excel通用工具类
- java开发导出Excel和Word文档
- PL/SQLDeveloper导入导出Oracle数据库方法
- PL/SQLDeveloper导入导出Oracle数据库方法
- MySQL 文本文件的导入导出数据的方法
- Springboot 最简单的结合MYSQL数据实现EXCEL表格导出及数据导入
- MySQL 文本文件的导入导出数据的方法
- 徐松亮软件应用教学-基于Visual Studio Code的python语言开发环境搭建
- Ionic!用Web技术开发移动应用!