C# test excel

最新推荐文章于 2025-05-20 11:26:11 发布

APP开发王

最新推荐文章于 2025-05-20 11:26:11 发布

阅读量269

点赞数

CC 4.0 BY-SA版权

分类专栏： C# 文章标签： c# integer excel string parameters exception

本文链接：https://blog.youkuaiyun.com/wgsnet/article/details/4389372

C# 专栏收录该内容

46 篇文章

订阅专栏

本文介绍了一个使用Visual Basic .NET实现的Excel自动化处理程序，该程序能够打开指定的Excel文件，进行特定的数据处理和格式设置，如在表格末尾添加求和行并设置边框样式，最后保存为新的文件。此程序还包含了错误处理和资源释放机制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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