如何仅为具有数据的行在Excel中添加边框 [英] How can i add borders in excel only for rows who have data

问题描述

大家好



我有一个应用程序，可以将listview导出为ex​​cel。

我想只为有行的行创建边框数据。

有人可以帮忙吗？

这是代码：

公开Sub saveExcelFile（ByVal FileName As String）
 Dim objXL As New Excel.Application 
 objXL = CType（CreateObject（Excel.Application），Application）
 Dim wbXL As Excel.Workbook 
 wbXL = CType（objXL.Workbooks.Add，Excel.Workbook）
 Dim wsXL As Excel.Worksheet 
 wsXL = CType（wbXL.Worksheets（1），Excel.Worksheet）
 Dim intRow，intCol As Integer''counter 
 Dim intTotalRow，intTotalCol As Integer''totalals 
 Dim strWorkSheetName As String =φύλακες
 Dim dt As New DateTime（Now.Ticks）
 Dim format As [String] =MM-dd-yyyy_hhmm
 Dim strDateTime As [String] = dt.ToString（format）
 Dim strExcelF ileName As String =ΤΕΘ& strDateTime& .xls
 Dim lvi作为ListViewItem 
 intTotalRow = lvRec.Items.Count 
 intTotalCol = lvRec.Columns.Count 
 wbXL = objXL.Workbooks.Add 
 wsXL = CType（objXL.ActiveSheet，Worksheet）
 wsXL.Name = strWorkSheetName 
对于intCol = 0到intTotalCol  -  1 
 wsXL.Cells（1，intCol + 1）= lvRec.Columns（intCol ）.Text 
下一个
 wsXL.Range（A1）。EntireRow.Font.Bold = True 
 wsXL.Range（A1）。EntireRow.Font.Name =Arial 
 wsXL.Range（A1）。EntireRow.Font.Size =12
 wsXL.Range（A2：A440）。EntireRow.Font.Name =Arial
 wsXL.Range（A2：A440）。EntireRow.Font.Size =11
 wsXL.Range（A1：R440）。Borders.LineStyle = XlLineStyle.xlContinuous 
 wsXL.Range（ A1，R1）。Interior.ColorIndex = 4 
 intRow = 0 
 For lvi in lvRec.Items 
 intRow + = 1 
 for intCol = 0 To lvi .SubItems.Count -  1 
 wsXL.Cells（intRow + 1，intCol + 1）= lvi.SubItems（intCol）.Text 
 Next 
 Next 
 wsXL.Rows.AutoFit（）
 wsXL.Columns.AutoFit（）
尝试
 objXL.ActiveWorkbook（）。SaveAs（strExcelFileName，Excel.XlFileFormat.xlWorkbookNormal）
 MsgBox（ΤοΑΑχχείοExcelδημιουργήθηκε！，MsgBoxStyle。信息，Πληροφορία）
 Catch ex As Exception 
 MsgBox（Αποτυχίααποθήκευσης！，MsgBoxStyle.Information，Πληροφορία）
结束尝试
 objXL.ActiveWorkbook.Close（错误）
 objXL.Quit（）
 objXL =没有
结束子

提前致谢

解决方案

尝试类似的事情：

 wsXL.Cells（intRow +  1 ，intCol +  1 ）。Borders.LineSty le = LineStyleType.Dot; 

更多： http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/93bb7ff7-0aed-4ce1-adca-aabde5fc3c2c [< a href =http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/93bb7ff7-0aed-4ce1-adca-aabde5fc3c2ctarget =_ blanktitle =New Window> ^

Hi guys

I have an application where export a listview to excel.
I want to make borders only for rows who have data.
Can anyone help?
Here is the code:

Public Sub saveExcelFile(ByVal FileName As String)
        Dim objXL As New Excel.Application
        objXL = CType(CreateObject("Excel.Application"), Application)
        Dim wbXL As Excel.Workbook
        wbXL = CType(objXL.Workbooks.Add, Excel.Workbook)
        Dim wsXL As Excel.Worksheet
        wsXL = CType(wbXL.Worksheets(1), Excel.Worksheet)
        Dim intRow, intCol As Integer '' counter
        Dim intTotalRow, intTotalCol As Integer '' totals
        Dim strWorkSheetName As String = "φύλακες"
        Dim dt As New DateTime(Now.Ticks)
        Dim format As [String] = "MM-dd-yyyy_hhmm"
        Dim strDateTime As [String] = dt.ToString(format)
        Dim strExcelFileName As String = "ΤΕΘ " & strDateTime & ".xls"
        Dim lvi As ListViewItem
        intTotalRow = lvRec.Items.Count
        intTotalCol = lvRec.Columns.Count
        wbXL = objXL.Workbooks.Add
        wsXL = CType(objXL.ActiveSheet, Worksheet)
        wsXL.Name = strWorkSheetName
        For intCol = 0 To intTotalCol - 1
            wsXL.Cells(1, intCol + 1) = lvRec.Columns(intCol).Text
        Next
        wsXL.Range("A1").EntireRow.Font.Bold = True
        wsXL.Range("A1").EntireRow.Font.Name = "Arial"
        wsXL.Range("A1").EntireRow.Font.Size = "12"
        wsXL.Range("A2:A440").EntireRow.Font.Name = "Arial"
        wsXL.Range("A2:A440").EntireRow.Font.Size = "11"
        wsXL.Range("A1:R440").Borders.LineStyle = XlLineStyle.xlContinuous
        wsXL.Range("A1", "R1").Interior.ColorIndex = 4
        intRow = 0
        For Each lvi In lvRec.Items
            intRow += 1
            For intCol = 0 To lvi.SubItems.Count - 1
                wsXL.Cells(intRow + 1, intCol + 1) = lvi.SubItems(intCol).Text
            Next
        Next
        wsXL.Rows.AutoFit()
        wsXL.Columns.AutoFit()
        Try
            objXL.ActiveWorkbook().SaveAs(strExcelFileName, Excel.XlFileFormat.xlWorkbookNormal)
            MsgBox("Το Αρχείο Excel δημιουργήθηκε!", MsgBoxStyle.Information, "Πληροφορία")
        Catch ex As Exception
            MsgBox("Αποτυχία αποθήκευσης!", MsgBoxStyle.Information, "Πληροφορία")
        End Try
        objXL.ActiveWorkbook.Close(False)
        objXL.Quit()
        objXL = Nothing
    End Sub

Thanks in advance

解决方案

Try something like that:

wsXL.Cells(intRow + 1, intCol + 1).Borders.LineStyle = LineStyleType.Dot; 

More: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/93bb7ff7-0aed-4ce1-adca-aabde5fc3c2c[^]

这篇关于如何仅为具有数据的行在Excel中添加边框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！