在做android的開發的時候,将資料儲存到SQLite資料庫中,有時候會要将儲存的這些資料導出成excel表格,這樣更友善檢視。通過查找資料,可以将資料庫中的表格轉化成
csv(Comma-Separated Values,CSV,有時也稱為字元分隔值,因為分隔字元也可以不是逗号)這種以純文字的模式儲存表格。還是以以前寫的一個模闆來實作點選打開連結,這裡就不放這個的代碼了,直接說将表格轉化成csv格式導出。
isi.java
package com.example.project_isi;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import android.app.Activity;
import android.app.AlertDialog;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.Environment;
import android.util.Log;
import android.view.MotionEvent;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.View.OnTouchListener;
import android.view.Window;
import android.widget.Button;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.RadioGroup.OnCheckedChangeListener;
import android.widget.RelativeLayout;
import android.widget.TextView;
import android.widget.Toast;
public class ISIActivity extends Activity{
private RelativeLayout rl;
private RelativeLayout rl2;
private int i = 0;
private String[] question = {"1、入睡困難:","2、維持睡眠困難:","3、早醒:","4、您對目前的睡眠模式滿意/不滿意程度如何?:",
"5、您認為您的失眠在多大程度上影響了您的日常功能:","6、您的失眠問題影響了您的生活品質,您覺得在别人眼中你的失眠程度如何?",
"7、您對目前的睡眠問題的擔心/痛苦程度:","您的得分:"};
private Button resultbutton;
private int j;
private String buttontext;
private int resulttext[] = new int[10];
private RadioButton button01;
private RadioButton button02;
private RadioButton button03;
private RadioButton button04;
private RadioButton button05;
private RadioGroup radioGroup;
private TextView questiontext;
private int score;
private Button databutton;
@Override
protected void onCreate(Bundle savedInstanceState) {
this.requestWindowFeature(Window.FEATURE_NO_TITLE);
super.onCreate(savedInstanceState);
setContentView(R.layout.isi);
questiontext = (TextView) findViewById(R.id.questiontitle);
radioGroup = (RadioGroup) findViewById(R.id.radiogroup);
button01 = (RadioButton) findViewById(R.id.button01);
button02 = (RadioButton) findViewById(R.id.button02);
button03 = (RadioButton) findViewById(R.id.button03);
button04 = (RadioButton) findViewById(R.id.button04);
button05 = (RadioButton) findViewById(R.id.button05);
databutton = (Button) findViewById(R.id.databutton);
rl = (RelativeLayout) findViewById(R.id.relativeLayout);
rl2 = (RelativeLayout) findViewById(R.id.relativeLayout2);
radioGroup.setOnCheckedChangeListener(new RadioGroupListener());
rl.setOnTouchListener(new OnTouchListener() {
@Override
public boolean onTouch(View arg0, MotionEvent arg1) {
rl.setVisibility(View.INVISIBLE);
rl2.setVisibility(View.VISIBLE);
return false;
}
});
databutton.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Intent intent = new Intent();
intent.setClass(ISIActivity.this, PatientInfoManage.class);
startActivity(intent);
}
});
final Button nextbutton = (Button) findViewById(R.id.nextbutton);
nextbutton.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
//questiontext.setText(question[i]);
//i ++;
if(button01.isChecked()==false && button02.isChecked()==false && button03.isChecked()==false
&& button04.isChecked()==false&& button05.isChecked()==false && i != 0){
new AlertDialog.Builder(ISIActivity.this)
.setTitle("溫馨提示")
.setMessage("您還沒有選擇任何一個選項!")
.setPositiveButton("确定", null)
.show();
return;
}
else{
questiontext.setText(question[i]);
if(i < question.length +1){
i++;
}
if(i == 1){
radioGroup.setVisibility(View.VISIBLE);
}
//i++;
radioGroup.clearCheck();
}
score += j;
System.out.println("result----->" + score);
resulttext[i]= j;
System.out.println("選擇的結果----》" + resulttext[i]);
//PInformation();
//question[7] = "您的得分: " + score;
if(i == question.length - 1){
nextbutton.setText("确定");
//score += j;
int total = score +j;
question[7] = "您的得分: " + total;
System.out.println("您的得分---" + total);
//PInformation();
//finish();
}
if(i == question.length ){
//finish();
PInformation();
radioGroup.setVisibility(View.INVISIBLE);
nextbutton.setVisibility(View.INVISIBLE);
}
}
});
}
public class RadioGroupListener implements OnCheckedChangeListener{
@Override
public void onCheckedChanged(RadioGroup group, int checkedId) {
if(checkedId == button01.getId()){
j = 0;
buttontext = button01.getText().toString();
System.out.println("buttontext ---" + buttontext);
}
if(checkedId == button02.getId()){
j = 1;
System.out.println("result11----->" + j);
buttontext = button02.getText().toString();
System.out.println("buttontext ---" + buttontext);
}
if(checkedId == button03.getId()){
j = 2;
buttontext = button03.getText().toString();
System.out.println("buttontext ---" + buttontext);
}
if(checkedId == button04.getId()){
j = 3;
buttontext = button04.getText().toString();
System.out.println("buttontext ---" + buttontext);
}
if(checkedId == button05.getId()){
j = 4;
buttontext = button05.getText().toString();
System.out.println("buttontext ---" + buttontext);
}
}
}
//獲得要儲存的資料
public void PInformation(){
int[] result = new int[8];
result[0] = resulttext[2];
result[1] = resulttext[3];
result[2] = resulttext[4];
result[3] = resulttext[5];
result[4] = resulttext[6];
result[5] = resulttext[7];
result[6] = resulttext[8];
result[7] = score;
String isi = "";
for(int i = 0;i < result.length;i++){
isi += String.valueOf(result[i]);
}
System.out.println("嚴重失眠指數----->" + isi);
PatientTest patientTest = new PatientTest();
patientTest.setIsi(isi);
PatientTestDAO ptdao = new PatientTestDAO(ISIActivity.this);
//獲得最大的id
//PatientInformationDAO pd = new PatientInformationDAO(ISIActivity.this);
ptdao.insert(patientTest);
System.out.println("目前ID----" + ptdao.getMaxId());
Toast.makeText(ISIActivity.this, "【失眠程度評估】資料儲存成功!", Toast.LENGTH_SHORT).show();
//
//String test_table = null;
Cursor c = ptdao.export();
ExportToCSV(c, "test_table.csv");
}
//導出表格的方法
public void ExportToCSV(Cursor c, String fileName) {
int rowCount = 0;
int colCount = 0;
FileWriter fw;
BufferedWriter bfw;
//擷取sd卡根目錄
File sdCardDir = Environment.getExternalStorageDirectory();
//儲存檔案目錄
File saveFile = new File(sdCardDir, fileName);
try {
rowCount = c.getCount();
colCount = c.getColumnCount();
fw = new FileWriter(saveFile);
bfw = new BufferedWriter(fw);
if (rowCount > 0) {
c.moveToFirst();
// 寫入表頭
for (int i = 0; i < colCount; i++) {
if (i != colCount - 1)
bfw.write(c.getColumnName(i) + ',');
else
bfw.write(c.getColumnName(i));
}
// 寫好表頭後換行
bfw.newLine();
// 寫入資料
for (int i = 0; i < rowCount; i++) {
c.moveToPosition(i);
// Toast.makeText(mContext, "正在導出第"+(i+1)+"條",
// Toast.LENGTH_SHORT).show();
Log.v("導出資料", "正在導出第" + (i + 1) + "條");
for (int j = 0; j < colCount; j++) {
if (j != colCount - 1)
bfw.write(c.getString(j) + ',');
else
bfw.write(c.getString(j));
}
// 寫好每條記錄後換行
bfw.newLine();
}
}
// 将緩存資料寫入檔案
bfw.flush();
// 釋放緩存
bfw.close();
// Toast.makeText(mContext, "導出完畢!", Toast.LENGTH_SHORT).show();
Log.v("導出資料", "導出完畢!");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
c.close();
}
}
}
上面有很多代碼都是以前儲存選項資訊的代碼,主要是最後一個方法實作格式的轉化。
要在AndroidManifest.xml加上一句對檔案的允許對sd卡可寫
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
最後得到的檔案名為test_table.csv,用excel打開得到的表格如下:
資料完整的轉化成了excel表格了。
然後是解析csv檔案,這個沒有布局檔案,
Mainactivity.java,由于csv檔案是用“,”分開的,在程式中就可以利用“,”将其分割開。
package com.example.testlistview;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.util.StringTokenizer;
import android.app.Activity;
import android.os.Bundle;
import android.os.Environment;
public class MainActivity extends Activity {
//獲得根目錄路徑
File sdCardDir = Environment.getExternalStorageDirectory();
int i = 0;
int j = 0;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
try {
File csv = new File(sdCardDir + "/test_table.csv"); // CSV檔案
BufferedReader br = new BufferedReader(new FileReader(csv));
br.readLine();
// 讀取直到最後一行
String line = "";
//這一行不為空
while ((line = br.readLine()) != null) {
i++;
//System.out.println("line---" + line);
// 把一行資料分割成多個字段
StringTokenizer st = new StringTokenizer(line, ",");
//周遊所有行,并列印出來,判斷有沒有分隔符
while (st.hasMoreTokens()) {
//j++;
// 每一行的多個字段用TAB隔開表示
//s[i] = st.nextToken();
System.out.print(st.nextToken());
// System.out.println("j ---" + j);
}
System.out.println();
// String item[] = line.split(",");//CSV格式檔案為逗号分隔符檔案,這裡根據逗号切分
//
// String last = item[item.length-1];//這就是你要的資料了
// j = item.length;
// System.out.println(last);
}
System.out.println("i----" + i);
System.out.println("j----" + j);
br.close();
} catch (Exception e) {
// 捕獲File對象生成時的異常
e.printStackTrace();
}
}
}
最後列印出來的資料為
:
這樣資料就完整的轉換過來了。