VB.NET OpenXml 修改制定Worksheet的特定单元格(外加log4net应用

app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
  </configSections>
  <log4net>
    <appender name="FileAppender" type="log4net.Appender.FileAppender">
      <file value="a.log"></file>
      <appendToFile value="true"></appendToFile>
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="[%d]-%p:%c=>%m%n"></conversionPattern>
      </layout>
    </appender>
    <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="[%d]-%p:%c=>%m%n"></conversionPattern>
      </layout>
    </appender>
    <root>
      <level value="DEBUG"></level>
      <appender-ref ref="FileAppender"></appender-ref>
    </root>
    <logger name="MyLogger">
      <level value="DEBUG"></level>
      <appender-ref ref="FileAppender"></appender-ref>
    </logger>
  </log4net>
</configuration>

Imports System.Windows.Forms
Imports System.IO
Imports log4net
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

<Assembly: log4net.Config.XmlConfiguratorAttribute(Watch:=True)> 
Module Module1
    Dim logger As ILog = log4net.LogManager.GetLogger(GetType(Module1))

    <STAThread()>
    Sub Main()
        Dim OFD As New OpenFileDialog
        Dim Original As String
        Dim Target As String
        Dim WbP As WorkbookPart
        Dim Sht As Sheet
        Dim WsP As WorksheetPart
        Dim Shd As SheetData
        Dim Row As Row
        Dim Cell As Cell

        OFD.Filter = "Excel Document|*.xlsx"
        OFD.Multiselect = False
        OFD.ShowDialog()

        Original = OFD.FileName
        logger.Info("Original File Path :" & Original)
        Target = AppDomain.CurrentDomain.BaseDirectory & "Test.xlsx"
        logger.Info("Target File Path :" & Target)
        File.Copy(Original,Target,True)
        logger.Info("File Copied")

        Using SSD As SpreadsheetDocument = SpreadsheetDocument.Open(Target,True)
            logger.Info("Spreadsheet Document Open")
            WbP = SSD.WorkbookPart
            Sht = WbP.Workbook.Descendants(Of Sheet)() _
                .Where(Function(S) S.Name = "Sheet1").FirstOrDefault()
            logger.Info("Sheet Id : " & Sht.Id.Value)
            WsP = WbP.GetPartById(Sht.Id)
            If Not WsP Is Nothing Then
                logger.Info("Get the WorksheetPart")
                Shd = WsP.Worksheet.Descendants(Of SheetData)().FirstOrDefault()
                Row = Shd.Descendants(Of Row)() _
                    .Where(Function(R) R.RowIndex.Value = 3).FirstOrDefault()
                logger.Info("Retrieve the Row")
                If Not Row Is Nothing Then
                    logger.Info("Row exists")
                    Cell = Row.Descendants(Of Cell)() _
                        .Where(Function(C) C.CellReference = "B3") _
                        .FirstOrDefault()
                    logger.Info("Retrieve the Cell")
                    If Not Cell Is Nothing Then
                        logger.Info("Cell exists")
                        Cell.CellValue.Text = "123456"
                    Else
                        logger.Info("Cell doesn't exist")
                        Cell = New Cell
                        Cell.CellReference = "B3"
                        Cell.CellValue = New CellValue
                        Cell.CellValue.Text = "123456"
                        Row.Append(Cell)
                        logger.Info("Append the cell")
                    End If
                    WbP.Workbook.Save()
                    logger.Info("Save changes")
                Else
                    logger.Info("Row doesn't exist")
                    Row = New Row
                    Row.RowIndex = 3
                    Cell = New Cell
                    Cell.CellReference = "B3"
                    Cell.CellValue = New CellValue
                    Cell.CellValue.Text = "123456"
                    Row.Append(Cell)
                    logger.Info("Append the cell")
                    Shd.Append(Row)
                    logger.Info("Append the row")
                End If
            Else
                logger.Error("WorksheetPart doesn't exist")
            End If
            Console.WriteLine("Finished")
            Console.ReadKey()
        End Using
    End Sub

End Module

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!