C# test excel

Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Dim BlankRow As Integer = 7 Dim logStrings As String Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim ci As New System.Globalization.CultureInfo("EN-US", True) System.Threading.Thread.CurrentThread.CurrentCulture = ci Dim Excel_file As String = "C:/1.xls" Dim Excel_oOutputXls As String = "c:/2.xls" Dim oAPP As New Excel.Application Dim oBook As Excel.Workbook Dim sheet As Excel.Worksheet = Nothing Dim missing As System.Reflection.Missing = System.Reflection.Missing.Value oBook = Nothing Try oBook = oAPP.Workbooks.Open(Excel_file, missing, missing, missing, missing, missing, _ missing, missing, missing, missing, missing, missing, _ missing, missing, missing) sheet = DirectCast(oBook.Worksheets.Item(1), Excel.Worksheet) sheet.Activate() 'at the end of the sheet rows add sum row for the APR-MAR column Dim sumRow As Integer = (BlankRow + 6) ' copyRange = sheet.Range("AA" & (sumRow).ToString(), missing) 'sheet.Range("AA" & (sumRow).ToString(), missing).Formula = "=SUM(AA" & (BlankRow + 1) & ":AA" & (sumRow - 1) & ")" Dim sumRange As Excel.Range sumRow = (16 + 1) sumRange = sheet.Range("D" & sumRow.ToString() & ":Q" & sumRow.ToString() & ",T" & sumRow.ToString() & ":AF" & sumRow.ToString() & ",AI" & sumRow.ToString() & ":AU" & sumRow.ToString() & ",AW" & sumRow.ToString() & ":BI" & sumRow.ToString()) sumRange.Borders.Weight = 2 sumRange.Borders.ColorIndex = 4 sumRange.Formula = "=Sum(" & "D" & (BlankRow + 1).ToString() & ":D" & (sumRow - 1).ToString() & ")" 'pasteRange.Borders.Color = Color.Blue.ToArgb() 'deledte old file, if not will case err DeletFile(Excel_oOutputXls) 'save as new file oBook.SaveAs(Excel_oOutputXls, missing, missing, missing, missing, missing, _ Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing) 'close without change template file oBook.Close(False, missing, missing) oAPP.Quit() GC.Collect() 'GC clear if not EXCEL.EXE process can not release. disply("Complete") Catch ex As Exception If oBook IsNot Nothing Then oBook.Close(False, missing, missing) End If oAPP.Quit() GC.Collect() disply("Err:" & ex.Message, Color.Red) End Try End Sub '********************************************************************************** '* Project : GenerateProductReport '* File Name : Form1.vb '* Function Name : disply '* Version : Ver M1.0 '* Modified Date : 2 Mar 2009 '* Author : Guang Shu '* Parameters : string '* Return Value : no '* Description : display info '* '************************************************************************************ Public Sub disply(ByVal strInput As String) logStrings += ("[" & DateTime.Now.ToString() & "] ") + strInput & vbCr & vbLf RichTextBox1.ForeColor = Color.Chartreuse RichTextBox1.AppendText(strInput & vbCr & vbLf) RichTextBox1.ScrollToCaret() RichTextBox1.Refresh() End Sub ' '********************************************************************************** ' '* Project : GenerateProductReport ' '* File Name : Form1.vb ' '* Function Name : disply ' '* Version : Ver M1.0 ' '* Modified Date : 27 Feb 2009 ' '* Author : Guang Shu ' '* Parameters : string;Color ' '* Return Value : no ' '* Description : display info ' '* ' '************************************************************************************ Public Sub disply(ByVal strInput As String, ByVal fontColor As Color) logStrings += ("[" & DateTime.Now.ToString() & "] ") + strInput & vbCr & vbLf RichTextBox1.AppendText(strInput & vbCr & vbLf) Dim p1 As Integer = RichTextBox1.Text.LastIndexOf(strInput) Dim p2 As Integer = strInput.Length RichTextBox1.ForeColor = Color.Chartreuse Try RichTextBox1.[Select](p1, p2) RichTextBox1.SelectionColor = fontColor Catch ex As Exception End Try RichTextBox1.Refresh() RichTextBox1.ScrollToCaret() End Sub Public Sub DeletFile(ByVal strfile As String) If System.IO.File.Exists(strfile) Then Try System.IO.File.Delete(strfile) Catch ex As System.IO.IOException disply(ex.ToString()) 'Console.WriteLine(); End Try End If End Sub End Class