This program can convert the data in the specified SQLite database to an Excel form
package zhaoxing.android.tool;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class DatabaseDump {
private String mDestXmlFilename;
private SQLiteDatabase mDb;
public DatabaseDump (SQLiteDatabase db, String destXml) {
mDb = db;
mDestXmlFilename = destXml;
}
public void exportData () {
try {
// Log.i ("mdb", mDb.getPath ());
// get the tables out of the given sqlite database
String sql = "SELECT * FROM sqlite_master";
Cursor cur = mDb.rawQuery (sql, new String [0]);
cur.moveToFirst ();
String tableName;
while (cur.getPosition () <cur.getCount ()) {
tableName = cur.getString (cur.getColumnIndex ("name"));
// don't process these two tables since they are used
// for metadata
if (! tableName.equals ("android_metadata")
&&! tableName.equals ("sqlite_sequence")) {
writeExcel (tableName);
}
cur.moveToNext ();
}
} catch (Exception e) {
e.printStackTrace ();
}
}
/ **
* Generate an Excel file
*
* @param fileName
* Excel file name to be generated
* /
public void writeExcel (String tableName) {
WritableWorkbook wwb = null;
String fileName;
fileName = "/ sdcard / QuestionData /" + tableName + ".xls";
int r = 0;
String sql = "select * from" + tableName;
Cursor cur = mDb.rawQuery (sql, new String [0]);
int numcols = cur.getColumnCount ();
int numrows = cur.getCount ();
// Log.i ("row", numrows + "");
// Log.i ("col", numcols + "");
String records [] [] = new String [numrows + 1] [numcols];
if (cur.moveToFirst ()) {
while (cur.getPosition () <cur.getCount ()) {
for (int c = 0; c <numcols; c ++) {
if (r == 0) {
records [r] [c] = cur.getColumnName (c);
records [r + 1] [c] = cur.getString (c);
} else {
records [r + 1] [c] = cur.getString (c);
}
// Log.i ("value" + r + "" + c, records [r] [c]);
}
cur.moveToNext ();
r ++;
}
cur.close ();
}
try {
// First use the factory method of the Workbook class to create a writable Workbook object
wwb = Workbook.createWorkbook (new File (fileName));
} catch (IOException e) {
e.printStackTrace ();
}
if (wwb! = null) {
// create a writable worksheet
// The createSheet method of Workbook has two parameters, the first is the name of the worksheet, and the second is the position of the worksheet in the workbook
WritableSheet ws = wwb.createSheet ("sheet1", 0);
// start adding cells below
for (int i = 0; i <numrows + 1; i ++) {
for (int j = 0; j <numcols; j ++) {
// Note here that in Excel, the first parameter represents the column and the second represents the row
Label labelC = new Label (j, i, records [i] [j]);
// Log.i ("Newvalue" + i + "" + j, records [i] [j]);
try {
// Add the generated cells to the worksheet
ws.addCell (labelC);
} catch (RowsExceededException e) {
e.printStackTrace ();
} catch (WriteException e) {
e.printStackTrace ();
}
}
}
try {
// write from memory to file
wwb.write ();
// close resources and release memory
wwb.close ();
} catch (IOException e) {
e.printStackTrace ();
} catch (WriteException e) {
e.printStackTrace ();
}
}
}
}