Excelシートからの読込
Excelシートから値を読込で、CSVファイルを作成するプログラムを作ってみた。
思いの外遅い。
string fname = "";
if (openFileDialog1.ShowDialog() == DialogResult.OK) {
fname = openFileDialog1.FileName;
}
if (fname == "") {
return;
}
exc = new Excel.Application();
Excel.Workbook wb =
exc.Workbooks.Open(fname,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
sheet = (Excel.Worksheet)wb.Worksheets[numericUpDown1.Value];
this.Cursor = Cursors.WaitCursor;
try {
//"F" から "BE" まで繰り返す
fl = new FileStream("TKCApl.CSV",FileMode.Create,FileAccess.Write);
string col1 = "";
string col2 = "";
if (textBox2.Text.Length == 1) {
col1 = "";
col2 = textBox2.Text;
} else {
col1 = textBox2.Text.Substring(0,1);
col2 = textBox2.Text.Substring(1,1);
}
string Stopcol1 = "";
string Stopcol2 = "";
if (textBox3.Text.Length == 1) {
Stopcol1 = "";
Stopcol2 = textBox3.Text;
} else {
Stopcol1 = textBox3.Text.Substring(0,1);
Stopcol2 = textBox3.Text.Substring(1,1);
}
textBox1.Clear();
while (true) {
this.Cursor = Cursors.WaitCursor;
GetOneCol(col1 + col2);
if )((col1 == Stopcol1) && (col2 == Stopcol2))(
break;
if (col2 == "Z") {
col1 = GetNextColName(col1);
col2 = COL_NAME[0];
} else {
col2 = GetNextColName(col2);
}
}
textBox1.Text = textStb.ToString();} finally {
fl.Close();exc.Workbooks.Close();
exc.Quit();
Marshal.ReleaseComObject(rn);
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(exc);
this.Cursor = Cursors.Default;
}
get_Renge ではなく、cellsの方が良いのかな〜？？？速度的に。
private readonly string[] COL_NAME = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P",
"Q","R","S","T","U","V","W","X","Y","Z"};private string GetNextColName(string col) {
if (col == "")
return COL_NAME[0];
for (int i = 0; i <= COL_NAME.Length -1; i++) {
if (col == COL_NAME[i]) {
return COL_NAME[++i];
}
}
return "";
}private void GetOneCol(string colname) {
StringBuilder stb = new StringBuilder(512);rn = sheet.get_Range(colname + "2",Type.Missing);
stb.Append(rn.Text.ToString());この後も値の取得が続く
Encoding en = Encoding.GetEncoding("Shift_JIS");
byte[] info = en.GetBytes(stb.ToString() + "\r\n");
fl.Write(info, 0, info.Length);}
