NPOI 将一个sheet指定区域复制到另一个sheet，复制数据，样式和备注

来源：互联网发布：linux 修改用户权限编辑：程序博客网时间：2024/06/06 04:09

    Private Sub CopyRange(ByRef fromSheet As HSSFSheet, ByRef toSheet As HSSFSheet, ByVal fromRowIndex As Integer, ByVal fromColIndex As Integer, ByVal toRowIndex As Integer, ByVal toColIndex As Integer, ByVal onlyData As Boolean, ByVal copyComment As Boolean)
        Dim sourceRow As HSSFRow = fromSheet.GetRow(fromRowIndex)
        Dim sourceCell As HSSFCell = sourceRow.GetCell(fromColIndex)
        If (Not sourceRow Is Nothing) And (Not sourceCell Is Nothing) Then

            Dim changingRow As HSSFRow = Nothing
            Dim changingCell As HSSFCell = Nothing
            changingRow = toSheet.GetRow(toRowIndex)
            If (changingRow Is Nothing) Then
                changingRow = toSheet.CreateRow(toRowIndex)
            End If

            changingCell = changingRow.GetCell(toColIndex)
            If (changingCell Is Nothing) Then
                changingCell = changingRow.CreateCell(toColIndex, sourceCell.CellType)
            End If

            If (onlyData) Then '仅数据
                '对单元格的值赋值
                If sourceCell.CellType = CellType.STRING Then
                    changingCell.SetCellType(CellType.STRING)
                    changingCell.SetCellValue(sourceCell.RichStringCellValue)
                End If
                changingCell.SetCellValue(sourceCell.ToString())
            Else         '非仅数据
                '单元格的编码
                changingCell.Encoding = sourceCell.Encoding
                '单元格的格式
                changingCell.CellStyle = sourceCell.CellStyle
                '单元格的公式
                changingCell.SetCellType(sourceCell.CellType)
                changingCell.SetCellValue(sourceCell.ToString())
                '对单元格的批注赋值
                If Not sourceCell.CellComment Is Nothing Then
                    Dim patr As HSSFPatriarch
                    Try
                        patr = toSheet.DrawingPatriarch
                    Catch ex As Exception
                        patr = CType(toSheet.CreateDrawingPatriarch(), HSSFPatriarch)
                    End Try

                    Dim tempCellCommentColumn As Integer = sourceCell.CellComment.Column + toColIndex - fromColIndex
                    Dim tempCellCommentRow As Integer = sourceCell.CellComment.Row + toRowIndex - fromRowIndex
                    Dim comment1 As HSSFComment = CType(patr.CreateCellComment(New HSSFClientAnchor(0, 0, 0, 0, tempCellCommentColumn, tempCellCommentRow, tempCellCommentColumn + 2, tempCellCommentRow + 2)), HSSFComment)
                    comment1.String = sourceCell.CellComment.String
                    comment1.Author = sourceCell.CellComment.Author
                    changingCell.CellComment = comment1
                End If

'changingCell.CellComment = sourceCell.CellComment
End If

End If

End Sub

0 0