以流方式导出到EXCEL时，怎么保持原有格式

以流方式导出到EXCEL时，如何保持原有格式

VB.NET code

 Public Shared Sub ExportToExcel(ByVal dgv As DataGridView, Optional ByVal strfilename As String = "")        Dim saveFileDialog As New SaveFileDialog()        saveFileDialog.Filter = "Execl files (*.xls)|*.xls"        saveFileDialog.FilterIndex = 0        saveFileDialog.FileName = strfilename        saveFileDialog.RestoreDirectory = True        'saveFileDialog.CreatePrompt = True        saveFileDialog.Title = "保存为Excel文件"        saveFileDialog.ShowDialog()        If saveFileDialog.FileName.IndexOf(":") < 0 Then            Exit Sub        End If        '被点了"取消"         Dim myStream As IO.FileStream        myStream = saveFileDialog.OpenFile()        Dim sw As New IO.StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0))        Dim columnTitle As String = ""        Try            '写入列标题             For i As Integer = 0 To dgv.ColumnCount - 1                If i > 0 Then                    columnTitle += vbTab                End If                columnTitle += dgv.Columns(i).HeaderText            Next            sw.WriteLine(columnTitle)            '写入列内容             For j As Integer = 0 To dgv.Rows.Count - 1                Dim columnValue As String = ""                For k As Integer = 0 To dgv.Columns.Count - 1                    If k > 0 Then                        columnValue += vbTab                    End If                    If dgv.Rows(j).Cells(k).Value Is Nothing Then                        columnValue += ""                    Else                        columnValue += dgv.Rows(j).Cells(k).FormattedValue.ToString.Trim()                    End If                Next                sw.WriteLine(columnValue)            Next            sw.Close()            myStream.Close()        Catch e As Exception            MessageBox.Show(e.ToString())        Finally            sw.Close()            myStream.Close()        End Try    End Sub

以上代码能够将datagridview表格内容顺利导出，可是表格有0001字样的内容，导出到excel时，显示为1了，数字再长些就用科学计数方式了，请各位帮忙啊

[解决办法]
详细的使用方法，请参照http://bingning.net/VB/SOURCE/index.html#programing。那里有EXCEL自定义类的说明。

VB.NET code

#Region "EXCEL用格式(边框、格式)"     ''' <summary>     ''' EXCEL用格式(边框、格式)     ''' </summary>     ''' <param name="iPosV">开始行号码</param>     ''' <param name="iPosH">开始列号码</param>     ''' <param name="iArrFmtInfo">格式情报</param>     ''' <param name="iArrPosInfo">位置情报</param>     ''' <param name="iRowCnt">数据行数</param>     ''' <param name="iColCnt">数据列数</param>     ''' <param name="bBorder">边框有无</param>     ''' <param name="bTitle">标题行有无</param>     ''' <returns>处理结果</returns>     ''' <remarks></remarks>     Private Function XlsEditTblFmt(ByVal iPosV As Integer, _                                    ByVal iPosH As Integer, _                                    ByVal iArrFmtInfo() As XLS_FMT, _                                    ByVal iArrPosInfo() As XLS_POS, _                                    ByVal iRowCnt As Integer, _                                    ByVal iColCnt As Integer, _                                    ByVal bBorder As Boolean, _                                    ByVal bTitle As Boolean) As XLS_RESULT         Dim iCol As Integer         Dim sRowS As String     'Excel字符串开始行号码         Dim sRowE As String     'Excel字符串结束行号码         Dim sColS As String     'Excel字符串开始列号码         Dim sColE As String     'Excel字符串结束列号码         Try             '标题行的开始行号码、开始列号码、结束列号码设定             sRowS = iPosV.ToString             sColS = GetXlsRange(iPosH)             sColE = GetXlsRange(iPosH + iColCnt - 1)             '标题行             If bTitle = True Then                 '标题行Range设定                 oRange = oSheet.Range(sColS & sRowS, sColE & sRowS)                  '标题行设定(背景色)                 oInterior = oRange.Interior                 oInterior.ColorIndex = Excel.XlPattern.xlPatternGrid                 MRComObject(oInterior)                  '标题行设定(居中)                 oRange.HorizontalAlignment = Excel.Constants.xlCenter                 MRComObject(oRange)             Else                 iPosV -= 1             End If              If iRowCnt > 0 Then                 '数据行开始行号码、结束行号码设定                 sRowS = (iPosV + 1).ToString                 sRowE = (iPosV + iRowCnt).ToString                 '数据行格式设定                 For iCol = 0 To iColCnt - 1                      '数据行的开始列号码设定                     sColS = GetXlsRange(iPosH + iCol)                     oRange = oSheet.Range(sColS & sRowS, sColS & sRowE)                     Select Case iArrFmtInfo(iCol)                         Case XLS_FMT.FMT_NORMAL        '通常设定                         Case XLS_FMT.FMT_NUMBER        '数值设定                             oRange.NumberFormatLocal = "#,##0"                         Case XLS_FMT.FMT_STRING        '字符串设定                             oRange.NumberFormatLocal = "@"                         Case XLS_FMT.FMT_DATE          '日期设定                             oRange.NumberFormatLocal = "yyyy/mm/dd"                         Case XLS_FMT.FMT_DECIMAL                             oRange.NumberFormatLocal = "0.000_ "                         Case XLS_FMT.FMT_DIGITONE     '小数点后一位                             oRange.NumberFormatLocal = "0.0_ "                         Case XLS_FMT.FMT_DIGITTWO      '小数点后二位                             oRange.NumberFormatLocal = "0.00_ "                         Case XLS_FMT.FMT_DIGITTHR       '小数点后三位                             oRange.NumberFormatLocal = "0.000_ "                     End Select                   MRComObject(oRange)                 Next             Return XLS_RESULT.XLS_OK         Catch ex As Exception             Me.XlsFileRelease()             Return XLS_RESULT.XLS_NG         End Try     End Function #End Region 
 
[解决办法]
mark
[解决办法]
数字以字符串的形式保存 

 

查看更多 下一篇