Convert data from SQLite to an Excel table

Source: Internet
Author: User

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 ();
}
}
}
} 
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.