C#操作Excel

標籤：alignment   .text   file   break   form   tostring   book   books   for   

public void DoMerge(string[] source)
{
Microsoft.Office.Interop.Excel.Application sourceApp = new Microsoft.Office.Interop.Excel.Application();
Workbooks sourceWbks = sourceApp.Workbooks;
_Workbook source_wbk = sourceWbks.Add(source[0]);
Sheets source_shs = source_wbk.Sheets;
Worksheet source_wsh = (Worksheet)source_shs[2];

//判斷有多少行內容
int lastRow = 2; //最後一列
for (; ; lastRow++)
{
string s = ((Range)source_wsh.Cells[lastRow, 1]).Text.ToString();
if (s == "")
break;
}

//合并檔案
//0已經讀作初始檔案了
for (int index = 1; index < source.Length; index++)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbooks wbks = app.Workbooks;
_Workbook _wbk = wbks.Add(source[index]);
Sheets shs = _wbk.Sheets;
Worksheet _wsh = (Worksheet)shs[2];
int rowCount = 2;
for (; ; rowCount++)
{
string s = ((Range)_wsh.Cells[rowCount, 1]).Text.ToString();
if (s == "")
break;
for (int colCount = 1; colCount <= MergeColCount; colCount++)
source_wsh.Cells[lastRow, colCount] = ((Range)_wsh.Cells[rowCount, colCount]).Text.ToString();
lastRow++;
}

app.Quit();
Form1.Kill(app);
}

//刪除合并後的BCDNP列
DeleteCol(source_wsh, 2);
DeleteCol(source_wsh, 2);
DeleteCol(source_wsh, 2);
DeleteCol(source_wsh, 11);
DeleteCol(source_wsh, 12);
DeleteCol(source_wsh, 12);

// 改日期欄格式

//逐行處理
for (int billRow = 2; ; billRow++)
{
string s = ((Range)source_wsh.Cells[billRow, 2]).Text.ToString();
if (s == "")
break;
//單號改成最後5位元字,
source_wsh.Cells[billRow, 2] = s.Substring(s.Length - 5);
//重量件數改數字
s = ((Range)source_wsh.Cells[billRow, 7]).Text.ToString();
Range myrange = (Range)source_wsh.Cells[billRow, 7];
myrange.NumberFormatLocal = "0.00_ ";
source_wsh.Cells[billRow, 7] = s;
myrange = (Range)source_wsh.Cells[billRow, 8];
myrange.NumberFormatLocal = "0";
source_wsh.Cells[billRow, 8] = ((Range)source_wsh.Cells[billRow, 8]).Text.ToString();
//修改日期欄格式
myrange = (Range)source_wsh.Cells[billRow, 1];
myrange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignGeneral;
myrange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
myrange.ShrinkToFit = true;
source_wsh.Cells[billRow, 1] = ((Range)source_wsh.Cells[billRow, 1]).Text.ToString();
}

FileInfo f = new FileInfo(source[0]);
string fileName = f.DirectoryName + "\\" + DateTime.Now.ToString("yyyyMMdd") + "轉運單.xlsx";
SaveFileDialog sfd = new SaveFileDialog();
//設定檔案類型
sfd.FileName = fileName;
if (sfd.ShowDialog() == DialogResult.OK)
{
source_wbk.SaveAs(sfd.FileName);
}

sourceApp.Quit();
Form1.Kill(sourceApp);//調用kill當前excel進程
}

C#操作Excel