作者：乔山办公网日期：
返回目录：excel表格制作
实现思路:
直接从Excel文件装换成XML文件是可以实现的,这里我采用一个中间装换,也就是先实现excel文件copy内容放入百实现设计好的access数据库文件中,然后再从access中读取记录写道xml文件中,因为我这里的实际问题是把多个excel表,每个表中又有多个sheet工作薄,这样我先导度入数据库中,也方便修改和处理.
这样,需要写两个类问:
XlsToAccess类,用来实现从excel文件根据事先约定的格式,按照数据库设计字段存放进去
AccessToXml类,用来实现从access文件数据库中的记录按约定格式写到xml文件中去
然后还需要一个主运行窗口类XlsToXml,这个类我们使用eclipse插件SWT Designer来完成
最后把工程打成答JAR包,再用NativeJ将Jar包生成为可执行的exe文件
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.jws.WebParam;
import jetsennet.common.PathUtil;
import jetsennet.contentmanage.CmDataAccess;
import jetsennet.contentmanage.CmObjHelper;
import jetsennet.net.UserAuthHeader;
import jetsennet.net.WSResult;
import jetsennet.sqlclient.ConnectionInfo;
import jetsennet.sqlclient.DbConfig;
import jetsennet.sqlclient.ISqlExecutor;
import jetsennet.sqlclient.QueryTable;
import jetsennet.sqlclient.SqlClientObjFactory;
import jetsennet.sqlclient.SqlCondition;
import jetsennet.sqlclient.SqlLogicType;
import jetsennet.sqlclient.SqlParamType;
import jetsennet.sqlclient.SqlQuery;
import jetsennet.sqlclient.SqlRelationType;
import jetsennet.sqlclient.TableJoinType;
import jetsennet.util.StringUtil;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
public class xmlToExcel
{
private static jetsennet.logger.ILog logger =
jetsennet.logger.LogManager.getLogger("JetsenNet.JCMP");
private static String XSD_FILE_PATH = "jcmp/schemafiles";
private static ConnectionInfo cmpConnectionString = new
ConnectionInfo(DbConfig.getProperty("cmp_driver"),
DbConfig.getProperty("cmp_dburl"),
DbConfig.getProperty("cmp_dbuser"),
DbConfig.getProperty("cmp_dbpwd"));
static jxl.write.WritableFont titleFont = new
jxl.write.WritableFont(WritableFont.createFont(" 宋 体 "), 10,
WritableFont.BOLD, false);
static WritableCellFormat titleFormat = new
jxl.write.WritableCellFormat(titleFont);
private static ConnectionInfo mccConnectionString = new
ConnectionInfo(DbConfig.getProperty("mcc_driver"),
DbConfig.getProperty("mcc_dburl"),
DbConfig.getProperty("mcc_dbuser"),
DbConfig.getProperty("mcc_dbpwd"));
private static ISqlExecutor sqlExecutor =
SqlClientObjFactory.createSqlExecutor(mccConnectionString);
static int celltitlenow = 0;// 当前列表头列号
static int cellvaluenow = 0;// 当前内容列号
static int rowtitlenow = 0;// 当前列表头行号
static boolean addcelltitle = true;
static String OldObjtype = "";
public static void main(String[] args)
{
File file = new File("C:/temp/test.xls");
WritableWorkbook workbook;
try
{
workbook = Workbook.createWorkbook(file);
//processActivity(workbook, "4023,4025,4007,4024,4026",
"200101,200103,200101,200102,200104");
processActivity(workbook, "4023,4025,4007",
"200101,200101,200101");
workbook.write();
workbook.close();
}
catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("OK?");
}
public static void processActivity(WritableWorkbook workBook, String
obj_id, String obj_subtype) throws Exception
{
OldObjtype = "";
celltitlenow = 0;
addcelltitle = true;
rowtitlenow =0;
WritableSheet sheet = workBook.createSheet("sheet1", 0);
/****/
/** 导出ID **/
String[] ids = obj_id.split(",");
/** 导出类型 **/
String[] idt = obj_subtype.split(",");
/** 导出个数 **/
int idsl = ids.length;
/** 定义列宽 **/
int cellwihth = 20;
xExcelType xt = new xExcelType();
xt.exShowType = 1;
if (xt.exShowType == 1)
{
addcelltitle = true;
}
else
{
addcelltitle = false;
}
// ******************主表/结构636f7079e799bee5baa6e997aee7ad94333定义
// 200101
xmlStruct mainXmlStruct1 = new xmlStruct();
mainXmlStruct1.tableName = "CM10_OBJECT";
mainXmlStruct1.tableCName = "活动信息";
mainXmlStruct1.tableCalssField = "OBJ_SUBTYPE";
mainXmlStruct1.tableCalssCName.put("200101", "院团大事");
mainXmlStruct1.tableCalssCName.put("200102", "院团出访");
mainXmlStruct1.tableCalssCName.put("200103", "院团专家交流");
HashMap test1 = new HashMap ();
test1.put("0", "新建");
test1.put("100", "完成");
mainXmlStruct1.tableFilesClassValue.put("OBJ_STATE", test1);
mainXmlStruct1.tableFiles = new String[][] { { "OBJ_NAME", "活
动名称" }, { "FIELD_1", "活动内容简介" }, { "FIELD_2", "活动开始时间" },
{ "FIELD_3", "活动结束时间" } };
xt.alltables.put("200101", mainXmlStruct1);
xt.alltables.put("200102", mainXmlStruct1);
xt.alltables.put("200103", mainXmlStruct1);
// 200104
xmlStruct mainXmlStruct2 = new xmlStruct();
mainXmlStruct2.tableName = "CM10_OBJECT";
mainXmlStruct2.tableCName = "活动信息";
mainXmlStruct2.tableCalssField = "OBJ_SUBTYPE";
mainXmlStruct2.tableCalssCName.put("200104", "院团演出");
mainXmlStruct2.tableFiles = new String[][] { { "OBJ_NAME", "活
动名称" }, { "FIELD_2", "活动开始时间" }, { "FIELD_3", "活动结束时间" } };
xt.alltables.put("200104", mainXmlStruct2);
// 20010401
xmlStruct mainXmlStruct3 = new xmlStruct();
mainXmlStruct3.tableName = "CM10_OBJECT";
mainXmlStruct3.tableCalssField = "OBJ_SUBTYPE";
mainXmlStruct3.tableCalssCName.put("20010401", "演出日志");
// HashMap showType_hm =
getHMctroWord("20010401");
// mainXmlStruct1.tableFilesClassValue.put("FIELD_3",
showType_hm);
mainXmlStruct3.tableFiles = new String[][] { { "FIELD_1", "演出
时间" }, { "FIELD_2", "演出场次" }, { "FIELD_3", "演出类型" }, { "NUM_VAL2",
"观众人数" } };
xt.alltables.put("20010401", mainXmlStruct3);
// ********************从表/结构定义
//
xmlStruct son1Struct1 = new xmlStruct();
son1Struct1.tableName = "CM10_MULTIINFO";
son1Struct1.tableCName = "动信息";
son1Struct1.tableCalssField = "INFO_TYPE";
son1Struct1.tableCalssFieldValue = "1";
// HashMap test2 = new HashMap ();
// test2.put("1", "新建1");
// test2.put("2", "完成2");
// test2.put("3", "完成3");
// xs2.tableFilesClassValue.put("INFO_TYPE", test2);
son1Struct1.tableFiles = new String[][] { { "FIELD_1", "活动地点
" } };
xt.alltables.put("200101-1", son1Struct1);
xt.alltables.put("200102-1", son1Struct1);
xt.alltables.put("200103-1", son1Struct1);
//
xmlStruct son1Struct2 = new xmlStruct();
son1Struct2.tableName = "CM10_MULTIINFO";
son1Struct2.tableCName = "人员信息";
son1Struct2.tableCalssField = "INFO_TYPE";
son1Struct2.tableCalssFieldValue = "2";
son1Struct2.tableFiles = new String[][] { { "FIELD_1", "参与人员
" } };
xt.alltables.put("200101-2", son1Struct2);
xt.alltables.put("200102-2", son1Struct2);
xt.alltables.put("200103-2", son1Struct2);
//
xmlStruct son1Struct3 = new xmlStruct();
son1Struct3.tableName = "CM10_MULTIINFO";
son1Struct3.tableCName = "剧目信息";
son1Struct3.tableCalssField = "INFO_TYPE";
son1Struct3.tableCalssFieldValue = "3";
son1Struct3.tableFiles = new String[][] { { "FIELD_1", "剧目名称
" } };
xt.alltables.put("200101-3", son1Struct3);
xt.alltables.put("200102-3", son1Struct3);
xt.alltables.put("200103-3", son1Struct3);
xmlStruct son2Struct1 = new xmlStruct();
son2Struct1.tableName = "CM10_MULTIINFO";
son2Struct1.tableCalssField = "INFO_TYPE";
son2Struct1.tableCalssFieldValue = "1";
son2Struct1.tableFiles = new String[][] { { "FIELD_1", "节目单" } };
xt.alltables.put("200104-1", son2Struct1);
xmlStruct son2Struct2 = new xmlStruct();
son2Struct2.tableName = "CM10_MULTIINFO";
son2Struct2.tableCalssField = "INFO_TYPE";
son2Struct2.tableCalssFieldValue = "2";
son2Struct2.tableFiles = new String[][] { { "FIELD_1", "剪报" } };
xt.alltables.put("200104-2", son2Struct2);
xmlStruct son2Struct3 = new xmlStruct();
son2Struct3.tableName = "CM10_MULTIINFO";
son2Struct3.tableCalssField = "INFO_TYPE";
son2Struct3.tableCalssFieldValue = "3";
son2Struct3.tableFiles = new String[][] { { "FIELD_1", "海报" } };
xt.alltables.put("200104-3", son2Struct3);
// 演出的日志多关联
xmlStruct son3Struct1 = new xmlStruct();
son3Struct1.tableName = "CM10_MULTIINFO";
son3Struct1.tableCalssField = "INFO_TYPE";
son3Struct1.tableCalssFieldValue = "1";
son3Struct1.tableFiles = new String[][] { { "FIELD_1", "演出单位
" } };
xt.alltables.put("20010401-1", son3Struct1);
xmlStruct son3Struct2 = new xmlStruct();
son3Struct2.tableName = "CM10_MULTIINFO";
son3Struct2.tableCalssField = "INFO_TYPE";
son3Struct2.tableCalssFieldValue = "2";
son3Struct2.tableFiles = new String[][] { { "FIELD_1", "演剧目名
称" } };
xt.alltables.put("20010401-2", son3Struct2);
xmlStruct son3Struct3 = new xmlStruct();
son3Struct3.tableName = "CM10_MULTIINFO";
son3Struct3.tableCalssField = "INFO_TYPE";
son3Struct3.tableCalssFieldValue = "3";
son3Struct3.tableFiles = new String[][] { { "FIELD_1", "演出地点
" } };
xt.alltables.put("20010401-3", son3Struct3);
xmlStruct son3Struct4 = new xmlStruct();
son3Struct4.tableName = "CM10_MULTIINFO";
son3Struct4.tableCalssField = "INFO_TYPE";
son3Struct4.tableCalssFieldValue = "4";
// son3Struct4.tableFilesClassValue.put("FIELD_2",
getHMctroWord("200104011"));
son3Struct4.tableFiles = new String[][] { { "FIELD_1", "参演人员
" }, { "FIELD_2", "责任方式" } };
xt.alltables.put("20010401-4", son3Struct4);
// //////////////下面是统一生成处理
int rowid = 0;
int cellid = 0;
for (int idi = 0; idi < idsl; idi++)
{
rowid = createcell(rowid, ids[idi], idt[idi], xt, sheet);
// 额外加入xml 结构
String objtype = idt[idi];
HashMap sonMainIdTypes = null;
if (objtype.equals("200101") || objtype.equals("200102") ||
objtype.equals("200103"))// 院团活动 常用
{
}
else if (objtype.equals("200104"))// 院团活动 演出
{
sonMainIdTypes = getMainSonIDAndType(ids[idi],
"20010401");
}
else if (objtype.equals("300201"))// 比赛声乐
{
}
else if (objtype.equals("400101") || objtype.equals("400201")
|| objtype.equals("400301") || objtype.equals("400401")
|| objtype.equals("400501"))// 通用比赛
{
}
else if (objtype.equals("500301"))// 优秀剧目展演
{
}
else if (objtype.equals("500101") ||
objtype.equals("500201"))
{
}
if (sonMainIdTypes != null)
{
Iterator iter = sonMainIdTypes.entrySet().iterator();
while (iter.hasNext())
{
Map.Entry entry = (Map.Entry) iter.next();
Object key = entry.getKey();
Object val = entry.getValue();
rowid = createcell(rowid, key.toString(),
val.toString(), xt, sheet);
}
}
}
}
1.用Excel 2003打开xml文档,点“确定”。2..Excel菜单“数据”--“列表”--“转换为区域”，点“确定”。
/**
*
* ExcelXML.java
* IBM_Developer_POI(Excel,Word) */
package com.wds.excelxml;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.NumberFormat;
import java.text.ParseException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import nu.xom.Attribute;
import nu.xom.Document;
import nu.xom.Element;
import nu.xom.Elements;
import nu.xom.Serializer;
public class ExcelXML {
public static void main(String[] args) {
excelXML();
}
/**
* 从Excel到XML
* 从XML到Excel
*/
private static void excelXML(){
/*
* 首先创建e799bee5baa6e79fa5e98193e78988e69d83365一个XML文档
* 要创建XML文档，首先创建一个根元素
*/
Element reportRoot=new Element("sheet");
Document xmlReport=new Document(reportRoot);
try {
//读取Excel文件
FileInputStream excelFIS=new FileInputStream("D:\\JavaTest\\Employee_List.xls");
//创建Excel工作表
HSSFWorkbook excelWB=new HSSFWorkbook(excelFIS);
//获得Excel工作簿
HSSFSheet excelSheet=excelWB.getSheetAt(0);
//获得工作簿的行数
int rows=excelSheet.getPhysicalNumberOfRows();
//遍历工作簿的行
for(int rowIndex=0; rowIndex<rows;rowIndex++){
HSSFRow oneRow=excelSheet.getRow(rowIndex);
if(oneRow==null){
continue;
}
//在迭代每一行的时候，创建xml的行元素
Element rowElement=new Element("row");
//获得当前行的单元格数
int cells=oneRow.getPhysicalNumberOfCells();
//遍历行中的每一个单元格
for(int cellIndex=0;cellIndex<cells;cellIndex++){
HSSFCell oneCell=oneRow.getCell(cellIndex);
if(oneCell==null){
continue;
}
//设置元素的默认名称
String elementName="header";
//获得单元格所在列位置
int cellColumnIndex=oneCell.getColumnIndex();
if(rowIndex>0){
elementName=reportRoot.getFirstChildElement("row").getChild(cellColumnIndex).getValue();
}
/*
* 去掉非法字符
*/
elementName = elementName.replaceAll("[\\P{ASCII}]","");
elementName = elementName.replaceAll(" ", "");
Element cellElement = new Element(elementName);
//添加属性和元素
//String attributeValue=oneCell.getCellStyle().getDataFormatString();
//Attribute dataFormatAttribute=new Attribute("dataFormat", attributeValue);
//cellElement.addAttribute(dataFormatAttribute);
/*
* 根据不同的属性添加
*/
Attribute strTypeAttribute=null;
switch (oneCell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strTypeAttribute=new Attribute("dataType","String");
cellElement.addAttribute(strTypeAttribute);
cellElement.appendChild(oneCell.getStringCellValue());
rowElement.appendChild(cellElement);
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strTypeAttribute=new Attribute("dataType","Numeric");
cellElement.addAttribute(strTypeAttribute);
HSSFDataFormatter dataFormatter=new HSSFDataFormatter();
String cellFormatted=dataFormatter.formatCellValue(oneCell);
cellElement.appendChild(cellFormatted);
rowElement.appendChild(cellElement);
break;
}
}
if(rowElement.getChildCount()>0){
reportRoot.appendChild(rowElement);
}
//System.out.println(xmlReport.toXML());
}