반응형
export sqlite To excel/csv 파일을 안드로이드로 구현하려면 어떻게 해야 합니까?
SQLite를 데이터베이스로 하는 안드로이드 앱을 개발하고 있습니다.특정 결과를 DB에서 엑셀 파일 형식으로 프로그래밍 방식으로 내보내고 해당 결과를 로컬 디바이스 경로에 저장하려고 합니다. 다음 링크를 발견했습니다.
그렇다면 안드로이드 앱용으로 Export to Excel을 구현하기 위한 정확한 절차는 무엇일까요?
여기 계신 여러분, 제가 성공적으로 구현한 답변입니다.
//new async task for file export to csv
private class ExportDatabaseCSVTask extends AsyncTask<String, String, Boolean> {
private final ProgressDialog dialog = new ProgressDialog(SearchResultActivity.this);
boolean memoryErr = false;
// to show Loading dialog box
@Override
protected void onPreExecute() {
this.dialog.setMessage("Exporting database...");
this.dialog.show();
}
// to write process
protected Boolean doInBackground(final String... args) {
boolean success = false;
String currentDateString = new SimpleDateFormat(Constants.SimpleDtFrmt_ddMMyyyy).format(new Date());
File dbFile = getDatabasePath("HLPL_FRETE.db");
Log.v(TAG, "Db path is: " + dbFile); // get the path of db
File exportDir = new File(Environment.getExternalStorageDirectory() + File.separator + Constants.FileNm.FILE_DIR_NM, "");
long freeBytesInternal = new File(getApplicationContext().getFilesDir().getAbsoluteFile().toString()).getFreeSpace();
long megAvailable = freeBytesInternal / 1048576;
if (megAvailable < 0.1) {
System.out.println("Please check"+megAvailable);
memoryErr = true;
}else {
exportDirStr = exportDir.toString();// to show in dialogbox
Log.v(TAG, "exportDir path::" + exportDir);
if (!exportDir.exists()) {
exportDir.mkdirs();
}
try {
List<SalesActivity> listdata = salesLst;
SalesActivity sa = null;
String lob = null;
for (int index = 0; index < listdata.size();) {
sa = listdata.get(index);
lob = sa.getLob();
break;
}
if (Constants.Common.OCEAN_LOB.equals(lob)) {
file = new File(exportDir, Constants.FileNm.FILE_OFS + currentDateString + ".csv");
} else {
file = new File(exportDir, Constants.FileNm.FILE_AFS + currentDateString + ".csv");
}
file.createNewFile();
CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
// this is the Column of the table and same for Header of CSV
// file
if (Constants.Common.OCEAN_LOB.equals(lob)) {
csvWrite.writeNext(Constants.FileNm.CSV_O_HEADER);
}else{
csvWrite.writeNext(Constants.FileNm.CSV_A_HEADER);
}
String arrStr1[] = { "SR.No", "CUTSOMER NAME", "PROSPECT", "PORT OF LOAD", "PORT OF DISCHARGE" };
csvWrite.writeNext(arrStr1);
if (listdata.size() > 0) {
for (int index = 0; index < listdata.size(); index++) {
sa = listdata.get(index);
String pol;
String pod;
if (Constants.Common.OCEAN_LOB.equals(sa.getLob())) {
pol = sa.getPortOfLoadingOENm();
pod = sa.getPortOfDischargeOENm();
} else {
pol = sa.getAirportOfLoadNm();
pod = sa.getAirportOfDischargeNm();
}
int srNo = index;
String arrStr[] = { String.valueOf(srNo + 1), sa.getCustomerNm(), sa.getProspectNm(), pol, pod };
csvWrite.writeNext(arrStr);
}
success = true;
}
csvWrite.close();
} catch (IOException e) {
Log.e("SearchResultActivity", e.getMessage(), e);
return success;
}
}
return success;
}
// close dialog and give msg
protected void onPostExecute(Boolean success) {
if (this.dialog.isShowing()) {
this.dialog.dismiss();
}
if (success) {
dialogBox(Constants.Flag.FLAG_EXPRT_S);
} else {
if (memoryErr==true) {
dialogBox(Constants.Flag.FLAG_MEMORY_ERR);
} else {
dialogBox(Constants.Flag.FLAG_EXPRT_F);
}
}
}
}
이게 내 답이야. 그리고 이건 작동해! 엑셀 파일은 .csv 파일과 같아.1단계: 이 jar 파일 https://code.google.com/p/opencsv/downloads/detail?name=opencsv-2.4.jar&can=2&q=을 다운로드합니다.
순서 2:
private class ExportDatabaseCSVTask extends AsyncTask<String ,String, String>{
private final ProgressDialog dialog = new ProgressDialog(MainActivity.this);
@Override
protected void onPreExecute() {
this.dialog.setMessage("Exporting database...");
this.dialog.show();
}
protected String doInBackground(final String... args){
File exportDir = new File(Environment.getExternalStorageDirectory(), "");
if (!exportDir.exists()) {
exportDir.mkdirs();
}
File file = new File(exportDir, "ExcelFile.csv");
try {
file.createNewFile();
CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
//data
ArrayList<String> listdata= new ArrayList<String>();
listdata.add("Aniket");
listdata.add("Shinde");
listdata.add("pune");
listdata.add("anything@anything");
//Headers
String arrStr1[] ={"First Name", "Last Name", "Address", "Email"};
csvWrite.writeNext(arrStr1);
String arrStr[] ={listdata.get(0), listdata.get(1), listdata.get(2), listdata.get(3)};
csvWrite.writeNext(arrStr);
csvWrite.close();
return "";
}
catch (IOException e){
Log.e("MainActivity", e.getMessage(), e);
return "";
}
}
@SuppressLint("NewApi")
@Override
protected void onPostExecute(final String success) {
if (this.dialog.isShowing()){
this.dialog.dismiss();
}
if (success.isEmpty()){
Toast.makeText(MainActivity.this, "Export successful!", Toast.LENGTH_SHORT).show();
}
else {
Toast.makeText(MainActivity.this, "Export failed!", Toast.LENGTH_SHORT).show();
}
}
}
기입하다Async.task 파일 내의 태스크
스텝 3: 이 작업을 호출 추가
ExportDatabaseCSVTask task=new ExportDatabaseCSVTask();
task.execute();
ExcelFile.csv파일이 sdcard에 생성됩니다.
ExportDatabaseCSV태스크:
public class ExportDatabaseCSVTask extends AsyncTask<String, Void, Boolean> {
private final ProgressDialog dialog = new ProgressDialog(MainActivity.this);
@Override
protected void onPreExecute() {
this.dialog.setMessage("Exporting database...");
this.dialog.show();
}
protected Boolean doInBackground(final String... args) {
String currentDBPath = "/data/"+ "your Package name" +"/databases/abc.db";
File dbFile = getDatabasePath(""+currentDBPath);
System.out.println(dbFile); // displays the data base path in your logcat
File exportDir = new File(Environment.getExternalStorageDirectory(), "/your Folder Name/");
if (!exportDir.exists()) { exportDir.mkdirs(); }
File file = new File(exportDir, "myfile.csv");
try {
file.createNewFile();
CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
Cursor curCSV = simpledb.rawQuery("select * from " + tablename,null);
csvWrite.writeNext(curCSV.getColumnNames());
while(curCSV.moveToNext()) {
String arrStr[]=null;
String[] mySecondStringArray = new String[curCSV.getColumnNames().length];
for(int i=0;i<curCSV.getColumnNames().length;i++)
{
mySecondStringArray[i] =curCSV.getString(i);
}
csvWrite.writeNext(mySecondStringArray);
}
csvWrite.close();
curCSV.close();
return true;
} catch (IOException e) {
Log.e("MainActivity", e.getMessage(), e);
return false;
}
}
protected void onPostExecute(final Boolean success) {
if (this.dialog.isShowing()) { this.dialog.dismiss(); }
if (success) {
Toast.makeText(MainActivity.this, "Export successful!", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(MainActivity.this, "Export failed", Toast.LENGTH_SHORT).show();
}
}
}
CSVWriter:
public class CSVWriter {
private PrintWriter pw;
private char separator;
private char quotechar;
private char escapechar;
private String lineEnd;
/** The character used for escaping quotes. */
public static final char DEFAULT_ESCAPE_CHARACTER = '"';
/** The default separator to use if none is supplied to the constructor. */
public static final char DEFAULT_SEPARATOR = ',';
/**
* The default quote character to use if none is supplied to the
* constructor.
*/
public static final char DEFAULT_QUOTE_CHARACTER = '"';
/** The quote constant to use when you wish to suppress all quoting. */
public static final char NO_QUOTE_CHARACTER = '\u0000';
/** The escape constant to use when you wish to suppress all escaping. */
public static final char NO_ESCAPE_CHARACTER = '\u0000';
/** Default line terminator uses platform encoding. */
public static final String DEFAULT_LINE_END = "\n";
/**
* Constructs CSVWriter using a comma for the separator.
*
* @param writer
* the writer to an underlying CSV source.
*/
public CSVWriter(Writer writer) {
this(writer, DEFAULT_SEPARATOR, DEFAULT_QUOTE_CHARACTER,
DEFAULT_ESCAPE_CHARACTER, DEFAULT_LINE_END);
}
/**
* Constructs CSVWriter with supplied separator, quote char, escape char and line ending.
*
* @param writer
* the writer to an underlying CSV source.
* @param separator
* the delimiter to use for separating entries
* @param quotechar
* the character to use for quoted elements
* @param escapechar
* the character to use for escaping quotechars or escapechars
* @param lineEnd
* the line feed terminator to use
*/
public CSVWriter(Writer writer, char separator, char quotechar, char escapechar, String lineEnd) {
this.pw = new PrintWriter(writer);
this.separator = separator;
this.quotechar = quotechar;
this.escapechar = escapechar;
this.lineEnd = lineEnd;
}
/**
* Writes the next line to the file.
*
* @param nextLine
* a string array with each comma-separated element as a separate
* entry.
*/
public void writeNext(String[] nextLine) {
if (nextLine == null)
return;
StringBuffer sb = new StringBuffer();
for (int i = 0; i < nextLine.length; i++) {
if (i != 0) {
sb.append(separator);
}
String nextElement = nextLine[i];
if (nextElement == null)
continue;
if (quotechar != NO_QUOTE_CHARACTER)
sb.append(quotechar);
for (int j = 0; j < nextElement.length(); j++) {
char nextChar = nextElement.charAt(j);
if (escapechar != NO_ESCAPE_CHARACTER && nextChar == quotechar) {
sb.append(escapechar).append(nextChar);
} else if (escapechar != NO_ESCAPE_CHARACTER && nextChar == escapechar) {
sb.append(escapechar).append(nextChar);
} else {
sb.append(nextChar);
}
}
if (quotechar != NO_QUOTE_CHARACTER)
sb.append(quotechar);
}
sb.append(lineEnd);
pw.write(sb.toString());
}
/**
* Flush underlying stream to writer.
*
* @throws IOException if bad things happen
*/
public void flush() throws IOException {
pw.flush();
}
/**
* Close the underlying stream writer flushing any buffered content.
*
* @throws IOException if bad things happen
*
*/
public void close() throws IOException {
pw.flush();
pw.close();
}
}
최근 앱에 엑셀 내보내기 기능을 구현했습니다.또한 전체 테이블 대신 필터링된 데이터를 Excel로 내보내는 방법에 대한 전체 코드를 포함했습니다.
이를 위해 두 번째 테이블을 작성해야 합니다.두 번째는 이 작업에 필요한 데이터를 보관하는 것입니다(두 번째 표에서는 자동 삽입 ID 열을 삭제했습니다.이는 엑셀 파일에 저장하지 않기 때문입니다).
먼저 두 번째 테이블을 클리어한 후 엔트리를 추가해야 합니다.
그런 다음 SqLiteToExcel 개체를 사용하여 db를 Excel로 내보내고 파일을 저장합니다.
그리고 excel 파일을 첨부하여 공유(이메일 이외의 다른 앱과 공유 가능)하는 이메일을 보냅니다.방법은 다음과 같습니다.
private void ExportData() {
//CHECK IF YOU HAVE WRITE PERMISSIONS OR RETURN
int permission = ActivityCompat.checkSelfPermission(getActivity(), Manifest.permission.WRITE_EXTERNAL_STORAGE);
if (permission != PackageManager.PERMISSION_GRANTED) {
Toast.makeText(getContext(), "Storage permissions not granted", Toast.LENGTH_SHORT).show();
return;
}
//get database object
myDbhelper = new MyDbHelper(getContext());
SQLiteDatabase database = myDbhelper.getWritableDatabase();
//delete all entries in the second table
database.delete("Table2",null,null);
//Create a cursor of the main database with your filters and sort order applied
Cursor cursor = getActivity().getContentResolver().query(
uri,
projections,
selection,
args,
sortOrder);
//loop through cursor and add entries from first table to second table
try {
while (cursor.moveToNext()) {
final String ColumnOneIndex = cursor.getString(cursor.getColumnIndexOrThrow("COLUMN_ONE"));
final String ColumnTwoIndex = cursor.getString(cursor.getColumnIndexOrThrow("COLUMN_TWO"));
final String ColumnThreeIndex = cursor.getString(cursor.getColumnIndexOrThrow("COLUMN_THREE"));
//add entries from table one into the table two
ContentValues values = new ContentValues();
values.put("TABLE2_COLUMN_1", ColumnOneIndex);
values.put("TABLE2_COLUMN_2", ColumnTwoIndex );
values.put("TABLE2_COLUMN_3", ColumnThreeIndex);
database.insert("table2", null, values);
}
} finally {
//close cursor after looping is complete
cursor.close();
}
//create a string for where you want to save the excel file
final String savePath = Environment.getExternalStorageDirectory() + "/excelfileTemp";
File file = new File(savePath);
if (!file.exists()) {
file.mkdirs();
}
//create the sqLiteToExcel object
SQLiteToExcel sqLiteToExcel = new SQLiteToExcel(getContext(), "databasefile.db",savePath);
//use sqLiteToExcel object to create the excel file
sqLiteToExcel.exportSingleTable("table2","excelfilename.xls", new SQLiteToExcel.ExportListener() {
@Override
public void onStart() {
}
@Override
public void onCompleted(String filePath) {
//now attach the excel file created and be directed to email activity
Uri newPath = Uri.parse("file://" + savePath + "/" +"excelfilename.xls");
StrictMode.VmPolicy.Builder builder = new StrictMode.VmPolicy.Builder();
StrictMode.setVmPolicy(builder.build());
Intent emailintent = new Intent(Intent.ACTION_SEND);
emailintent.setType("application/vnd.ms-excel");
emailintent.putExtra(Intent.EXTRA_SUBJECT, "Subject");
emailintent.putExtra(Intent.EXTRA_TEXT, "I'm email body.");
emailintent.putExtra(Intent.EXTRA_STREAM,newPath);
startActivity(Intent.createChooser(emailintent, "Send Email"));
}
@Override
public void onError(Exception e) {
System.out.println("Error msg: " + e);
Toast.makeText(getContext(), "Failed to Export data", Toast.LENGTH_SHORT).show();
}
});
}
내 앱에 이 메서드가 구현되어 있으며 작동한다.
CSV 형식은 각 행의 "string, string, string /n"입니다. "은 컬럼 구분자이고 행의 "/n"입니다.데이터베이스에서 데이터를 가져와 다음과 같이 내보냅니다.
public static Boolean exportToCSV(List<Data> data, File file) {
try {
final String head = "ValueX, ValueY \n";
if (!file.exists()) {
file.createNewFile();
}
FileWriter fileWriter = new FileWriter(file.getAbsoluteFile());
BufferedWriter writer = new BufferedWriter(fileWriter);
writer.write(head);
for (Item item : items) {
final String line = String.format("%s,%s\n",
item.getValueX(),
item.getValueY());
writer.write(line);
}
writer.close();
} catch (IOException e) {
return false;
}
return true;
}
언급URL : https://stackoverflow.com/questions/21448001/how-to-implement-export-sqlite-to-excel-csv-file-in-android
반응형
'programing' 카테고리의 다른 글
| powershell의 Null 병합 (0) | 2023.04.16 |
|---|---|
| Git에서 특정 파일을 무시하는 방법 (0) | 2023.04.16 |
| OS X의 'date' 명령어에는 ISO 8601 '-I' 옵션이 없습니다. (0) | 2023.04.16 |
| NSDate를 NSString으로 변환 (0) | 2023.04.16 |
| 첫 번째 인수를 제외한 모든 인수를 처리합니다(bash 스크립트에서). (0) | 2023.04.16 |