excel to xml java_java怎么操作excel转xml文件

展开全部

package com.asima;

import java.io.FileInputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**

*

* @author asima

* @data 2006-10-18

*/

public class XlsToAccess

{

HSSFSheet globalSheet = null;

/*读取一个指定单32313133353236313431303231363533e58685e5aeb931333337623537元格内容*/

public String readCellValue(String pos)

{

int xpos;

short ypos;

int cellType; /*取得此单元格的类型 0-Numeric,1-String,3-null*/

String result; /*返回取得的单元格的值*/

ypos = (short) (pos.toUpperCase().charAt(0) - 65);

xpos = Integer.parseInt(pos.substring(1, pos.length())) - 1;

HSSFRow row = null; /* 定义excel中的行 */

HSSFCell cell = null; /* 定义excel中的单元格 */

/* 根据xPos和yPos取得单元格 */

row = globalSheet.getRow(xpos);

cell = row.getCell(ypos);

/** **************此处如果是空需要修改********************************** */

cellType = cell.getCellType();

switch (cellType)

{

case 0: /* 0-Numeric */

result = String.valueOf(cell.getNumericCellValue());

break;

case 1: /* 1-String */

result = cell.getStringCellValue();

break;

case 3: /* 3-null */

result = "";

break;

default:

result = "";

break;

}

return result;

}

/*读取excel文件并把内容插入到access表中*/

public void insertIntoTable() throws Exception

{

// 创建对Excel工作簿文件的引用

HSSFWorkbook workbook =

new HSSFWorkbook(new FileInputStream("D:/temp/test.xls"));

// 获得一个sheet

globalSheet = workbook.getSheetAt(0);

String value1 = readCellValue("c1");

String value2 = readCellValue("c2");

String value3 = readCellValue("c3");

String value4 = readCellValue("c4");

System.out.println(value1);

System.out.println(value2);

/* 插入数据库 */

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

String url = "jdbc:odbc:asima";

Connection conn = DriverManager.getConnection(url);

PreparedStatement stmt =

conn.prepareStatement("insert into custom values(?,?,?,?)");

// 定义查询的SQL语句

stmt.setString(1, value1);

stmt.setString(2, value2);

stmt.setString(3, value3);

stmt.setString(4, value4);

stmt.executeUpdate();

stmt.close(); // 关闭statement

conn.close(); // 关闭连接

}

}

8. 编写代码AccessToXml.java

package com.asima;

import java.io.FileOutputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.Statement;

import org.jdom.Document;

import org.jdom.Element;

import org.jdom.output.Format;

import org.jdom.output.XMLOutputter;

/**

*

* @author asima

* @data 2006-10-18

*/

public class AccessToXml

{

public void buildXML() throws Exception

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

String url = "jdbc:odbc:asima";

// Connection conn = DriverManager.getConnection(url,"","");

Connection conn = DriverManager.getConnection(url);

Statement stmt = conn.createStatement();

// 创建一个statement

String sql = "select * from custom"; // 定义查询的SQL语句

ResultSet rs = stmt.executeQuery(sql); // 执行查询

// 创建文档

Document document = new Document(new Element("联系人列表"));

ResultSetMetaData rsmd = rs.getMetaData(); // 获取字段名

int numberOfColumns = rsmd.getColumnCount(); // 获取字段数

int i = 0;

while (rs.next())

{ // 将查询结果取出

Element element0 = new Element("联系人");

//创建元素 生成JDOM树

document.getRootElement().addContent(element0);

for (i = 1; i <= numberOfColumns; i++)

{

//xml编码转换

String date = rs.getString(i);

Element element =

new Element(rsmd.getColumnName(i)).setText(date);

element0.addContent(element);

}

}

rs.close(); // 关闭结果集

stmt.close(); // 关闭statement

conn.close(); // 关闭连接

XMLOutputter outp = new XMLOutputter();

Format fm = org.jdom.output.Format.getPrettyFormat();

fm.setEncoding("GB2312");

outp.setFormat(fm);

// 输出XML文档

outp.output(document, new FileOutputStream("d:/temp/test2.xml"));

System.out.print("XML 文档生成完毕！");

}

}

9. 编写代码XlsToXml.java

package com.asima;

import org.eclipse.jface.dialogs.MessageDialog;

import org.eclipse.swt.SWT;

import org.eclipse.swt.events.SelectionAdapter;

import org.eclipse.swt.events.SelectionEvent;

import org.eclipse.swt.widgets.Button;

import org.eclipse.swt.widgets.Display;

import org.eclipse.swt.widgets.Label;

import org.eclipse.swt.widgets.Shell;

import org.eclipse.swt.widgets.Text;

/**

*

* @author asima

* @data 2006-10-18

*/

public class XlsToXml

{

/**

* Launch the application

* @param args

*/

public static void main(String[] args)

{

final Display display = Display.getDefault();

final Shell shell = new Shell();

shell.setSize(500, 375);

shell.setText("excel文件转化成xml文件");

shell.open();

final Label label = new Label(shell, SWT.NONE);

label.setText("参数内容");

label.setBounds(15, 25, 67, 16);

final Text text = new Text(shell, SWT.BORDER);

text.setBounds(88, 22, 175, 20);

final Button button = new Button(shell, SWT.NONE);

button.addSelectionListener(new SelectionAdapter() {

public void widgetSelected(final SelectionEvent e)

{

XlsToAccess aa = new XlsToAccess();

try

{

aa.insertIntoTable();

}

catch(Exception ex)

{

System.out.println(ex);

}

MessageDialog.openInformation(null,"","导入Access数据库成功!");

}

});

button.setText("Excel到access");

button.setBounds(50, 105, 125, 55);

final Button button_1 = new Button(shell, SWT.NONE);

button_1.addSelectionListener(new SelectionAdapter() {

public void widgetSelected(final SelectionEvent e)

{

AccessToXml bb = new AccessToXml();

try

{

bb.buildXML();

}

catch(Exception ex)

{

System.out.println(ex);

}

MessageDialog.openInformation(null,"","生成XML文件成功!");

}

});

button_1.setText("从access到xml");

button_1.setBounds(195, 110, 140, 50);

shell.layout();

while (!shell.isDisposed())

{

if (!display.readAndDispatch())

display.sleep();

}

}

}

已赞过

已踩过<

你对这个回答的评价是？

评论

收起