1
2
3
4
5 package oscon2006.common;
6
7 import java.io.File;
8 import java.io.FileInputStream;
9 import java.io.FileNotFoundException;
10 import java.io.FileOutputStream;
11 import java.io.IOException;
12 import java.io.InputStream;
13 import java.sql.SQLException;
14 import java.util.ArrayList;
15 import java.util.Iterator;
16 import java.util.List;
17 import java.util.Map;
18
19 import org.apache.poi.hssf.usermodel.HSSFCell;
20 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
21 import org.apache.poi.hssf.usermodel.HSSFRow;
22 import org.apache.poi.hssf.usermodel.HSSFSheet;
23 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
24
25 import com.healthmarketscience.jackcess.Column;
26 import com.healthmarketscience.jackcess.DataType;
27 import com.healthmarketscience.jackcess.Database;
28 import com.healthmarketscience.jackcess.Table;
29
30 public class Util
31 {
32 private Util()
33 {
34 }
35
36 public static File createDatabaseFile(File xlsFile, String dbFilename)
37 throws FileNotFoundException, IOException, SQLException
38 {
39 InputStream in = new FileInputStream(xlsFile);
40 HSSFWorkbook wb = new HSSFWorkbook(in);
41
42 return createDatabaseFile(wb, dbFilename);
43 }
44
45 public static File createDatabaseFile(HSSFWorkbook wb, String dbFilename)
46 throws SQLException, IOException
47 {
48
49 File mdbFile = new File(dbFilename);
50
51 Database db = Database.create(mdbFile);
52
53 int sheets = wb.getNumberOfSheets();
54
55 for (int sheetIndex = 0; sheetIndex < sheets; sheetIndex++)
56 {
57 Table table = createTable(db, wb, sheetIndex);
58
59 HSSFSheet sheet = wb.getSheetAt(sheetIndex);
60
61 int firstRowIndex = sheet.getFirstRowNum();
62 int lastRowIndex = sheet.getLastRowNum();
63
64 for (int rowIndex = firstRowIndex + 1; rowIndex < lastRowIndex; rowIndex++)
65 {
66 HSSFRow row = sheet.getRow(rowIndex);
67 addRow(table, row);
68 }
69 }
70
71 db.close();
72
73 return mdbFile;
74 }
75
76 private static void addRow(Table t, HSSFRow row)
77 throws IOException
78 {
79 Iterator iter = row.cellIterator();
80
81 List<String> rowData = new ArrayList<String>();
82
83 while (iter.hasNext())
84 {
85 HSSFCell c = (HSSFCell) iter.next();
86 rowData.add(c.getStringCellValue());
87 }
88
89 t.addRow(rowData.toArray(new Object[rowData.size()]));
90
91 }
92
93 private static Table createTable(Database db, HSSFWorkbook wb, int sheetIndex)
94 throws IOException, SQLException
95 {
96
97 HSSFSheet sheet = wb.getSheetAt(sheetIndex);
98 String tableName = wb.getSheetName(sheetIndex);
99
100 int firstRowIndex = sheet.getFirstRowNum();
101
102 HSSFRow firstRow = sheet.getRow(firstRowIndex);
103
104 Iterator cellIter = firstRow.cellIterator();
105
106 List<Column> columns = new ArrayList<Column>();
107
108 while (cellIter.hasNext())
109 {
110 HSSFCell cell = (HSSFCell) cellIter.next();
111 Column c = new Column();
112 c.setType(DataType.TEXT);
113 c.setName(cell.getStringCellValue());
114 columns.add(c);
115 }
116 db.createTable(tableName, columns);
117
118 return db.getTable(tableName);
119
120 }
121
122 public static File createExcelFile(File mdbFile, String xlsFilename)
123 throws IOException, SQLException
124 {
125 Database db = Database.open(mdbFile);
126
127 HSSFWorkbook wb = createWorkbook(db);
128
129 File xlsFile = new File(xlsFilename);
130
131 FileOutputStream fos = null;
132
133 try
134 {
135 fos = new FileOutputStream(xlsFile);
136 wb.write(fos);
137 fos.flush();
138
139 return xlsFile;
140 }
141 catch (IOException ex)
142 {
143 fos.close();
144 xlsFile.delete();
145 throw ex;
146 }
147
148 }
149
150 public static HSSFWorkbook createWorkbook(Database db) throws IOException, SQLException
151 {
152 HSSFWorkbook wb = null;
153
154 wb = new HSSFWorkbook();
155
156 int sheetNumber = 0;
157
158 for (String tableName : db.getTableNames())
159 {
160 HSSFSheet sheet = wb.createSheet();
161 wb.setSheetName(sheetNumber, tableName);
162 Table t = db.getTable(tableName);
163
164 int spreadsheetRowNumber = 0;
165
166
167 short cellIndex = 0;
168
169 HSSFRow headerRow = sheet.createRow(spreadsheetRowNumber++);
170
171 List<com.healthmarketscience.jackcess.Column> columns = t.getColumns();
172 for (com.healthmarketscience.jackcess.Column c : columns)
173 {
174 HSSFCell cell = headerRow.createCell(cellIndex);
175 cell.setCellValue(c.getName().toUpperCase());
176 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
177 cellIndex++;
178 }
179
180
181 Map<String, Object> databaseRow;
182
183 while ((databaseRow = t.getNextRow()) != null)
184 {
185 cellIndex = 0;
186
187 for (String columnName : databaseRow.keySet())
188 {
189 HSSFRow spreadsheetRow = sheet.createRow(spreadsheetRowNumber);
190 HSSFCell c = spreadsheetRow.createCell(cellIndex);
191 c.setCellType(HSSFCell.CELL_TYPE_STRING);
192 c.setCellValue(String.valueOf(databaseRow.get(columnName)));
193 cellIndex++;
194 }
195 spreadsheetRowNumber++;
196 }
197 sheetNumber++;
198 }
199
200 return wb;
201
202 }
203
204 }