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

app.config

[html]
<?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> 

Module1.vb

[vb]
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 

摘自 TX_OfficeDev的专栏