- 浏览: 16240 次
- 性别:
- 来自: 武汉
文章分类
最新评论
/**
* 将数据写入Excel中
*/
public String exportExcel(String filepath,String sheetName,long clientID)throws Exception{
String returnPath = "";
long thistime = new Date().getTime();
OutputStream os;
Connection con = null;
ResultSet rs = null;
PreparedStatement ps = null;
try{
// 生成文件
os = new FileOutputStream(filepath + sheetName + "-" + thistime + ".xls");
WritableWorkbook book = Workbook.createWorkbook(os);
// sheet名称
WritableSheet sheet = book.createSheet(sheetName, 0);
//设置格式
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);
WritableCellFormat cellFormat=new WritableCellFormat(wf);
//设置为文本格式
NumberFormat nf = new NumberFormat("#00");
WritableCellFormat cf = new WritableCellFormat(nf);
//设置为金额
NumberFormat nf2 = new NumberFormat("#,##0.00");
WritableCellFormat wcf = new WritableCellFormat(nf2);
cellFormat.setAlignment(Alignment.CENTRE);
cellFormat.setBackground(jxl.format.Colour.SKY_BLUE);//设置单元格的颜色
sheet.setColumnView(0, 20);
sheet.setColumnView(1, 30);
sheet.setColumnView(2, 30);
sheet.setColumnView(3, 20);
sheet.setColumnView(4, 20);
WritableCellFormat cellFormat1=new WritableCellFormat();
cellFormat1.setAlignment(Alignment.CENTRE);
//设置列
Label four_1 = new Label(0, 0, "合同号");
four_1.setCellFormat(cellFormat);
//four_1.setCellFormat(cf);
sheet.addCell(four_1);
Label four_2 = new Label(1, 0, "收款方账户");
four_2.setCellFormat(cellFormat);
//four_2.setCellFormat(cf);
sheet.addCell(four_2);
Label four_3 = new Label(2, 0, "付款方账户");
four_3.setCellFormat(cellFormat);
//four_3.setCellFormat(cf);
sheet.addCell(four_3);
Label four_4 = new Label(3, 0, "金额");
four_4.setCellFormat(cellFormat);
//four_4.setCellFormat(wcf);
sheet.addCell(four_4);
Label four_5 = new Label(4, 0, "摘要");
four_5.setCellFormat(cellFormat);
//four_5.setCellFormat(cf);
sheet.addCell(four_5);
int x=0;
String sql="select * from SETT_CONSIGNRECEIVESET c where c.npayeeclientid="+clientID;
con = Database.getConnection();
ps=con.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
x++;
Label label1=new Label(0,x,rs.getString("SCONTRACTCODE"),cf);
sheet.addCell(label1);
Label label2=new Label(1,x,NameRef.getAccountNum(rs.getLong("NPAYEEACCTID")),cf);
sheet.addCell(label2);
Label label3=new Label(2,x,NameRef.getAccountNum(rs.getLong("NPAYERACCTID")),cf);
sheet.addCell(label3);
Label label4=new Label(3,x,"",wcf);
sheet.addCell(label4);
Label label5=new Label(4,x,"",cf);
sheet.addCell(label5);
}
book.write();
book.close();
returnPath=filepath + sheetName + "-" + thistime + ".xls";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
try
{
if (rs != null)
{
rs.close();
rs = null;
}
if (ps != null)
{
ps.close();
ps = null;
}
if (con != null)
{
con.close();
con = null;
}
}
catch (Exception e)
{
;
}
}
return returnPath;
}
/**
* 从Excel中导入数据
* @param mySmartUpload
* @param uploadPath
* @param sessionMng
* @return map
* @throws Exception
*/
public Map importExcel(SmartUpload mySmartUpload, String uploadPath,SessionOB sessionMng) throws Exception {
Vector vector = new Vector();
Map map = new HashMap();
Vector returnVector = new Vector();
String strAdd = "";// 每次从上传文件中读到的一个单元。
short index = 0;
boolean bIsValid = false;
File myfile = mySmartUpload.getFiles().getFile(0);
myfile.saveAs(uploadPath + "special/consignreceive/"+myfile.getFileName());
Connection conn = null;
FileInputStream fis = new FileInputStream(Env.UPLOAD_PATH+"special/consignreceive/"+myfile.getFileName());
ConsignReceiveInfo consignReceiveInfo = null;
if(myfile.getFileName().indexOf(".xls") < 0) {
throw new Exception("选择文档与模板文件不一致,请检查");
}
try {
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
if (workbook != null) {
sheet = workbook.getSheetAt(0);
}
if (sheet == null) {
throw new Exception("不能导入空的Excel文件");
}
if (sheet != null) {
row = sheet.getRow(1);
}
if(row == null){
throw new Exception("不能导入空的Excel文件");
}
for (int j=1; row!=null; j++, row=sheet.getRow(j)) {
bIsValid = true;
consignReceiveInfo = new ConsignReceiveInfo();
//1.合同号 2.收款方账号 3.付款方账号 4.金额 5.摘要
long subTypeId = -1;
for(index = 1; index <= 5; index++){
strAdd = "";
cell = row.getCell((short) (index - 1));
if (cell != null) {
// 在Excel中的类型是文本、常规
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
strAdd = cell.getStringCellValue();
}
// 在Excel中的类型是数值
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
strAdd = String.valueOf(cell.getNumericCellValue());
}
System.out.println(strAdd);
}
if (strAdd == null) {
strAdd = "";
}
if(index==1){
consignReceiveInfo.setSContractCode(strAdd);
}
if (index == 2) {
consignReceiveInfo.setNPayeeAcctID((Long)NameRef.getByNoFromSett(strAdd).get("id"));
consignReceiveInfo.setNPayeeClientID((Long)NameRef.getByNoFromSett(strAdd).get("nclientid"));
}
if(index==3){
consignReceiveInfo.setNPayerAcctID((Long)NameRef.getByNoFromSett(strAdd).get("id"));
consignReceiveInfo.setNPayerClientID((Long)NameRef.getByNoFromSett(strAdd).get("nclientid"));
}
if (index == 4) {
if (strAdd.matches("^\\s*([1-9]\\d{0,12}|0)(\\.(\\d){1,2})?\\s*$")) {
consignReceiveInfo.setMAmount(Double.valueOf(strAdd.replaceAll(",", "")).doubleValue());
}
else {
UpLoanReturnInfo upLoanReturnInfo = new UpLoanReturnInfo();
upLoanReturnInfo.setIsOk(true);
upLoanReturnInfo.setPositionCol(index);
upLoanReturnInfo.setPositionRow(j+1);
upLoanReturnInfo.setReason("金额格式不正确");
returnVector.addElement(upLoanReturnInfo);
}
}
if(index==5){
UpLoanReturnInfo upLoanReturnInfo = new UpLoanReturnInfo();
if(strAdd != null && !strAdd.equals("") && strAdd.length() > 0){
if(NameRef.getNAbstractIDByName(strAdd)>0){
consignReceiveInfo.setNAbstractID(NameRef.getNAbstractIDByName(strAdd));
}else{
upLoanReturnInfo.setIsOk(true);
upLoanReturnInfo.setPositionCol(index);
upLoanReturnInfo.setPositionRow(j+1);
upLoanReturnInfo.setReason("摘要不存在,请检查!");
returnVector.addElement(upLoanReturnInfo);
}
}else{
upLoanReturnInfo.setIsOk(true);
upLoanReturnInfo.setPositionCol(index);
upLoanReturnInfo.setPositionRow(j+1);
upLoanReturnInfo.setReason("摘要填写不正确,请检查!");
returnVector.addElement(upLoanReturnInfo);
}
}
}
consignReceiveInfo.setNOfficeID(sessionMng.m_lOfficeID);
consignReceiveInfo.setNCurrencyID(sessionMng.m_lCurrencyID);
consignReceiveInfo.setDTInput(Env.getSystemDate());
consignReceiveInfo.setNInputUserID(sessionMng.m_lUserID);
//consignReceiveInfo.setNPayeeClientID(sessionMng.m_lClientID);
consignReceiveInfo.setNStatus(OBConstant.SettInstrStatus.SAVE);
consignReceiveInfo.setNTransType(31);
vector.add(consignReceiveInfo);
}
//判断格式是否正确 如果正确则插入数据库
if(returnVector.size()==0){
int length=vector.size();
ConsignReceiveInfo[] infos=new ConsignReceiveInfo[length];
if (length > 0) {
for (int i = 0; i < length; i++) {
ConsignReceiveInfo info = (ConsignReceiveInfo) vector.get(i);
infos[i] = info;
}
//插入操作
}
////删除已经插入的操作
conn = Database.getConnection();
conn.setAutoCommit(false);
ConsignReceiveDao consignReceiveDao=new ConsignReceiveDao(conn);
for(int i=0;i<infos.length;i++){
ConsignReceiveInfo dataEntity = infos[i];
consignReceiveDao.add(dataEntity);
}
conn.commit();
conn.close();
//consignReceiveDao.importFromExcel(infos);
} else {
map.put("returnVector", returnVector);
}
}catch(Exception e){
e.printStackTrace();
conn.rollback();
}
return map;
}
}
* 将数据写入Excel中
*/
public String exportExcel(String filepath,String sheetName,long clientID)throws Exception{
String returnPath = "";
long thistime = new Date().getTime();
OutputStream os;
Connection con = null;
ResultSet rs = null;
PreparedStatement ps = null;
try{
// 生成文件
os = new FileOutputStream(filepath + sheetName + "-" + thistime + ".xls");
WritableWorkbook book = Workbook.createWorkbook(os);
// sheet名称
WritableSheet sheet = book.createSheet(sheetName, 0);
//设置格式
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);
WritableCellFormat cellFormat=new WritableCellFormat(wf);
//设置为文本格式
NumberFormat nf = new NumberFormat("#00");
WritableCellFormat cf = new WritableCellFormat(nf);
//设置为金额
NumberFormat nf2 = new NumberFormat("#,##0.00");
WritableCellFormat wcf = new WritableCellFormat(nf2);
cellFormat.setAlignment(Alignment.CENTRE);
cellFormat.setBackground(jxl.format.Colour.SKY_BLUE);//设置单元格的颜色
sheet.setColumnView(0, 20);
sheet.setColumnView(1, 30);
sheet.setColumnView(2, 30);
sheet.setColumnView(3, 20);
sheet.setColumnView(4, 20);
WritableCellFormat cellFormat1=new WritableCellFormat();
cellFormat1.setAlignment(Alignment.CENTRE);
//设置列
Label four_1 = new Label(0, 0, "合同号");
four_1.setCellFormat(cellFormat);
//four_1.setCellFormat(cf);
sheet.addCell(four_1);
Label four_2 = new Label(1, 0, "收款方账户");
four_2.setCellFormat(cellFormat);
//four_2.setCellFormat(cf);
sheet.addCell(four_2);
Label four_3 = new Label(2, 0, "付款方账户");
four_3.setCellFormat(cellFormat);
//four_3.setCellFormat(cf);
sheet.addCell(four_3);
Label four_4 = new Label(3, 0, "金额");
four_4.setCellFormat(cellFormat);
//four_4.setCellFormat(wcf);
sheet.addCell(four_4);
Label four_5 = new Label(4, 0, "摘要");
four_5.setCellFormat(cellFormat);
//four_5.setCellFormat(cf);
sheet.addCell(four_5);
int x=0;
String sql="select * from SETT_CONSIGNRECEIVESET c where c.npayeeclientid="+clientID;
con = Database.getConnection();
ps=con.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
x++;
Label label1=new Label(0,x,rs.getString("SCONTRACTCODE"),cf);
sheet.addCell(label1);
Label label2=new Label(1,x,NameRef.getAccountNum(rs.getLong("NPAYEEACCTID")),cf);
sheet.addCell(label2);
Label label3=new Label(2,x,NameRef.getAccountNum(rs.getLong("NPAYERACCTID")),cf);
sheet.addCell(label3);
Label label4=new Label(3,x,"",wcf);
sheet.addCell(label4);
Label label5=new Label(4,x,"",cf);
sheet.addCell(label5);
}
book.write();
book.close();
returnPath=filepath + sheetName + "-" + thistime + ".xls";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
try
{
if (rs != null)
{
rs.close();
rs = null;
}
if (ps != null)
{
ps.close();
ps = null;
}
if (con != null)
{
con.close();
con = null;
}
}
catch (Exception e)
{
;
}
}
return returnPath;
}
/**
* 从Excel中导入数据
* @param mySmartUpload
* @param uploadPath
* @param sessionMng
* @return map
* @throws Exception
*/
public Map importExcel(SmartUpload mySmartUpload, String uploadPath,SessionOB sessionMng) throws Exception {
Vector vector = new Vector();
Map map = new HashMap();
Vector returnVector = new Vector();
String strAdd = "";// 每次从上传文件中读到的一个单元。
short index = 0;
boolean bIsValid = false;
File myfile = mySmartUpload.getFiles().getFile(0);
myfile.saveAs(uploadPath + "special/consignreceive/"+myfile.getFileName());
Connection conn = null;
FileInputStream fis = new FileInputStream(Env.UPLOAD_PATH+"special/consignreceive/"+myfile.getFileName());
ConsignReceiveInfo consignReceiveInfo = null;
if(myfile.getFileName().indexOf(".xls") < 0) {
throw new Exception("选择文档与模板文件不一致,请检查");
}
try {
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
if (workbook != null) {
sheet = workbook.getSheetAt(0);
}
if (sheet == null) {
throw new Exception("不能导入空的Excel文件");
}
if (sheet != null) {
row = sheet.getRow(1);
}
if(row == null){
throw new Exception("不能导入空的Excel文件");
}
for (int j=1; row!=null; j++, row=sheet.getRow(j)) {
bIsValid = true;
consignReceiveInfo = new ConsignReceiveInfo();
//1.合同号 2.收款方账号 3.付款方账号 4.金额 5.摘要
long subTypeId = -1;
for(index = 1; index <= 5; index++){
strAdd = "";
cell = row.getCell((short) (index - 1));
if (cell != null) {
// 在Excel中的类型是文本、常规
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
strAdd = cell.getStringCellValue();
}
// 在Excel中的类型是数值
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
strAdd = String.valueOf(cell.getNumericCellValue());
}
System.out.println(strAdd);
}
if (strAdd == null) {
strAdd = "";
}
if(index==1){
consignReceiveInfo.setSContractCode(strAdd);
}
if (index == 2) {
consignReceiveInfo.setNPayeeAcctID((Long)NameRef.getByNoFromSett(strAdd).get("id"));
consignReceiveInfo.setNPayeeClientID((Long)NameRef.getByNoFromSett(strAdd).get("nclientid"));
}
if(index==3){
consignReceiveInfo.setNPayerAcctID((Long)NameRef.getByNoFromSett(strAdd).get("id"));
consignReceiveInfo.setNPayerClientID((Long)NameRef.getByNoFromSett(strAdd).get("nclientid"));
}
if (index == 4) {
if (strAdd.matches("^\\s*([1-9]\\d{0,12}|0)(\\.(\\d){1,2})?\\s*$")) {
consignReceiveInfo.setMAmount(Double.valueOf(strAdd.replaceAll(",", "")).doubleValue());
}
else {
UpLoanReturnInfo upLoanReturnInfo = new UpLoanReturnInfo();
upLoanReturnInfo.setIsOk(true);
upLoanReturnInfo.setPositionCol(index);
upLoanReturnInfo.setPositionRow(j+1);
upLoanReturnInfo.setReason("金额格式不正确");
returnVector.addElement(upLoanReturnInfo);
}
}
if(index==5){
UpLoanReturnInfo upLoanReturnInfo = new UpLoanReturnInfo();
if(strAdd != null && !strAdd.equals("") && strAdd.length() > 0){
if(NameRef.getNAbstractIDByName(strAdd)>0){
consignReceiveInfo.setNAbstractID(NameRef.getNAbstractIDByName(strAdd));
}else{
upLoanReturnInfo.setIsOk(true);
upLoanReturnInfo.setPositionCol(index);
upLoanReturnInfo.setPositionRow(j+1);
upLoanReturnInfo.setReason("摘要不存在,请检查!");
returnVector.addElement(upLoanReturnInfo);
}
}else{
upLoanReturnInfo.setIsOk(true);
upLoanReturnInfo.setPositionCol(index);
upLoanReturnInfo.setPositionRow(j+1);
upLoanReturnInfo.setReason("摘要填写不正确,请检查!");
returnVector.addElement(upLoanReturnInfo);
}
}
}
consignReceiveInfo.setNOfficeID(sessionMng.m_lOfficeID);
consignReceiveInfo.setNCurrencyID(sessionMng.m_lCurrencyID);
consignReceiveInfo.setDTInput(Env.getSystemDate());
consignReceiveInfo.setNInputUserID(sessionMng.m_lUserID);
//consignReceiveInfo.setNPayeeClientID(sessionMng.m_lClientID);
consignReceiveInfo.setNStatus(OBConstant.SettInstrStatus.SAVE);
consignReceiveInfo.setNTransType(31);
vector.add(consignReceiveInfo);
}
//判断格式是否正确 如果正确则插入数据库
if(returnVector.size()==0){
int length=vector.size();
ConsignReceiveInfo[] infos=new ConsignReceiveInfo[length];
if (length > 0) {
for (int i = 0; i < length; i++) {
ConsignReceiveInfo info = (ConsignReceiveInfo) vector.get(i);
infos[i] = info;
}
//插入操作
}
////删除已经插入的操作
conn = Database.getConnection();
conn.setAutoCommit(false);
ConsignReceiveDao consignReceiveDao=new ConsignReceiveDao(conn);
for(int i=0;i<infos.length;i++){
ConsignReceiveInfo dataEntity = infos[i];
consignReceiveDao.add(dataEntity);
}
conn.commit();
conn.close();
//consignReceiveDao.importFromExcel(infos);
} else {
map.put("returnVector", returnVector);
}
}catch(Exception e){
e.printStackTrace();
conn.rollback();
}
return map;
}
}
发表评论
-
json数据处理
2016-06-22 15:16 456public static <T> T rea ... -
java的同步异步调用
2016-03-06 18:09 853软件模块之间总是存在着一定的接口,从调用方式上,可以把他们分 ... -
shiro初级认识
2016-02-29 18:21 791一、 介绍: shiro是apache提供的强大而灵活 ... -
校验身份证号码及编码规则
2015-12-10 15:52 768身份证前6位【ABCDEF】为行政区划数字代码(简称数字码) ... -
org.apache.commons.httpclient的jar包的使用
2015-01-21 15:15 25691.创建HttpClient实例 HttpClient ... -
checkbox实现全选
2014-12-12 10:54 619<%@ page contentType=" ... -
servlet实现多文件上传
2014-12-12 10:44 5291.fileupload.jsp<%@ page l ... -
servlet实现文件的下载
2014-12-12 09:53 476public class DownLoadServlet ex ... -
jxl对Excel的操作,包含合并单元格
2014-12-11 15:50 159101.import java.io.*; 02.im ... -
文件的拷贝和转移
2014-10-21 10:16 537package com.wepull.demo; im ...
相关推荐
Delphi Excel数据导入到数据库 需要 uses ComObj; 具体使用方法在文档中有说明
实现打开Excel表路径,然后再MFC中将Excel表中数据取出存储到Access数据库中
asp.net中将excel表中的数据导入添加到数据库中的源码
SQL Server,MySQL在开发中)中的数据直接导出到打开的任意表格中(支持MS Excel和WPS表格),也可以从任意打开表格中将数据导入到数据库中,还可以将变更前的数据先保存到XML中,数据变化后,可以变化的数据导出到...
VB中将EXCEL的数据导入到数据库的表中,并连接数据库,进行一些基本的操作
在命令行键入“DCOMCNFG”,则进入COM组件配置界面,选择Microsoft Excel后点击属性按钮,将三个单选项一律选择自定义,编辑中将Everyone加入所有权限。保存完毕后重新启动服务器。 客户端的环境配置没发现什么特别...
软件最大的功能是将数据库中的数据直接导出到打开的任意表格中,软件还支持从任意打开表格中将数据导入到数据库中、将变更前的数据先保存到XML中、以及数据变化后,可以变化的数据导出到Excel中,将变更的字段标色以...
3、用软件安装包建立新的数据库,然后在系统管理——系统数据管理中将TXT文档导入到软件中; 4、导入完毕后检查基础数据,如商品简称、是否积分、分店变价、计价方式等。 原理: 将整理好的数据转换成可被思迅软件...
SQLite帮助 一个允许轻松集成SQLite数据库... 以下代码段显示了如何创建表以及如何从数据库中插入和检索对象: SQLiteHelper sHelp = new SQLiteHelper(this, ); ArrayList<String> cols = new ArrayList<String
JavaWeb中将数据从Excel表导入到数据库的实例,对应博客http://blog.csdn.net/tmaskboy/article/details/51755305
用Kettle从excel中将导入oracle数据库的简单方法-附件资源
试试这个链接http://www.sqlines.com/postgresql/npgsql_cs_result_sets[^]
在asp中将excel导入SQL server数据库 简单代码
里面有excel测试数据、创建表,xml文件,注意,xml文件内容中将数据库字段与excel表格里的标题行相匹配
在asp.net中将SQL Sever中的数据导入到excel中的方法汇总、、、我都用过,挺好用的
基于Qt下实现的增删改查与导入,将MySQL数据显示到tableview里,再实现增删改查。此外还增加一个从excel表中将数据导入到数据库里。
昨天写小项目的时候遇到了一个需求:把txt文档的数据导入到mysql数据库中,开始本来想直接用Mysql Workbench导入TXT文件,但是最后发现不支持TXT导入,结果我吧嗒吧嗒的去把TXT转了Excel,拿到Linux上导入的时候又...