Wednesday, May 28, 2014

NPOI delete cell background color

Imports NPOI.SS.UserModel
Imports NPOI.HSSF.UserModel
Imports System.IO
--------------------------------------
Public Sub DeleteCellBackColor()
        Dim srcWB As HSSFWorkbook = Nothing
        Try
            Using fs As New FileStream("C:\temp\Book1.xls", FileMode.Open, FileAccess.Read)
                srcWB = New HSSFWorkbook(fs, True)
            End Using

            'Get sheet by index
            Dim sheet As HSSFSheet = srcWB.GetSheetAt(0)

            For rowNum As Integer = 0 To sheet.LastRowNum
                'Check row is exist
                Dim row As HSSFRow = sheet.GetRow(rowNum)
                If row Is Nothing Then Continue For

                For clmNum As Integer = 0 To row.LastCellNum
                    'Check cell is exist
                    Dim cell As HSSFCell = row.GetCell(clmNum)
                    If cell Is Nothing Then Continue For

                    'Check cell back color is exist
                    Dim cellStyle As HSSFCellStyle = cell.CellStyle
                    If cellStyle Is Nothing Then Continue For

                    'Delete cell back color
                    cellStyle.FillPattern = FillPattern.NoFill
                Next
            Next

            'Save file
            Using fs As New FileStream("C:\temp\Book2.xls", FileMode.OpenOrCreate, FileAccess.Write)
                srcWB.Write(fs)
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub
-----------------------
Input file

----------------------------------------------
Output file



No comments:

Post a Comment