001: package com.avaje.util.seedgen;
002:
003: import java.io.File;
004: import java.io.FileWriter;
005: import java.io.IOException;
006: import java.io.PrintStream;
007: import java.io.Writer;
008:
009: import jxl.Sheet;
010: import jxl.Workbook;
011:
012: /**
013: * Generates insert sql statements from an excel spreadsheet.
014: * <p>
015: * Used to generate seed data scripts to populate a database.
016: * </p>
017: * <p>
018: * Note that the sheets are in the same order that the statements are written to
019: * the file.
020: * </p>
021: */
022: public class XlSeedGenerator {
023:
024: /**
025: * The default log is to the console. Expect to be a cmd line utility.
026: */
027: PrintStream log = System.out;
028:
029: /**
030: * The end of line characters.
031: */
032: String newLine = "\r\n";
033:
034: /**
035: * Run the generate. Pass in two parameters. The first is the source xls
036: * file name, the second is the destination file.
037: */
038: public static void main(String[] args) throws Exception {
039:
040: String source = "model/seed.xls";
041: String dest = null;
042:
043: XlSeedGenerator wk = new XlSeedGenerator();
044: if (args.length > 0) {
045: source = args[0];
046: }
047: if (args.length > 1) {
048: dest = args[1];
049: }
050: wk.generate(source, dest);
051: }
052:
053: /**
054: * Create the XlSeedGenerator.
055: */
056: public XlSeedGenerator() {
057:
058: }
059:
060: /**
061: * Change where to log messages to. Can set this to null.
062: */
063: public void setLog(PrintStream log) {
064: this .log = log;
065: }
066:
067: /**
068: * Change the new line characters.
069: */
070: public void setNewLine(String newLine) {
071: this .newLine = newLine;
072: }
073:
074: /**
075: * Generate the sql file from the source xls file.
076: *
077: * @param source the source xls file
078: * @param dest the source file to write
079: */
080: public void generate(String source, String dest) throws Exception {
081:
082: File srcFile = new File(source);
083: if (!srcFile.exists()) {
084: throw new IOException("The source file [" + source
085: + "] was not found");
086: }
087: Workbook workbook = Workbook.getWorkbook(srcFile);
088:
089: if (dest == null) {
090: dest = source + ".seed.sql";
091: }
092: File destFile = new File(dest);
093: FileWriter fileWriter = new FileWriter(destFile);
094:
095: generate(workbook, fileWriter);
096:
097: fileWriter.flush();
098: fileWriter.close();
099: }
100:
101: /**
102: * Generate the sql and write it to the writer.
103: */
104: private void generate(Workbook workbook, Writer destWriter)
105: throws IOException {
106:
107: XlSeedlInsertGenerator gen = new XlSeedlInsertGenerator();
108:
109: int numSheets = workbook.getNumberOfSheets();
110:
111: for (int i = 0; i < numSheets; i++) {
112: Sheet sheet = workbook.getSheet(i);
113: XlSeedDataSheet dataSheet = new XlSeedDataSheet(sheet);
114:
115: if (dataSheet.isEmpty()) {
116: logMessage("Sheet [" + i
117: + "] ignored as empty (needs min 3 rows)");
118:
119: } else {
120: String msg = gen.generate(destWriter, dataSheet);
121: logMessage("Generated " + msg);
122:
123: destWriter.write(newLine);
124: }
125: }
126: }
127:
128: /**
129: * Log a message for user feedback.
130: */
131: private void logMessage(String msg) {
132: if (log != null) {
133: log.println(msg);
134: }
135: }
136: }
|