高手进入：EXCEL内存读写，帮顶有分。
在进行EXCEL报表系统开发，每次将EXCEL表格从数据库中读出并在临时文件夹生成EXCEL文件再让EXCEL读取，保存时在读取为数据流存入数据库，中间有两次读写盘对速度有影响突然想到为什么不能从数据流直接注入EXCEL中呢？自己试验使用ReadProcessMemory、WriteProcessMemory连内存数据也没读到，请高人帮忙。
目前最高100，发帖后再加分或别外开贴加分。
[解决办法]
EXCEL有特殊格式，你在内存如何读写
*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码)
http://feiyun0112.cnblogs.com/
[解决办法]
帮顶，学习
[解决办法]
没有试过读取内存中的数据，学习！
[解决办法]
没明白LZ所说
[解决办法]
顶下，，
[解决办法]
注入到EXCEL进和读出EXCEL内存数据流之间有数据操作吗？
[解决办法]
你就是说
1、从数据库读取数据
2、保存到EXCEL文件
3、从EXCEL文件读取数据
4、保存到数据库
不知是否是上面的流程？
[解决办法]
帮顶。。。给分。呵呵
[解决办法]
EXCEL 导入 SQL 的方法
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
[解决办法]
根据站点所在机器的EXCEL版本替换上面的版本
[解决办法]
帮顶有分么？那就顶一个。
[解决办法]
[解决办法]
不知道怎样读取在内存中的EXCEL数据流,只知道文本流。不懂！
学习+接分 o(∩_∩)o...哈哈
[解决办法]
这是一个老外写的不需要安装EXCEL就可以创建EXCEL文件的类，可能对你有帮助。
Public Enum ValueTypes xlsInteger = 0 xlsNumber = 1 xlsText = 2 End Enum 'enum to hold cell alignment Public Enum CellAlignment xlsGeneralAlign = 0 xlsLeftAlign = 1 xlsCentreAlign = 2 xlsRightAlign = 3 xlsFillCell = 4 xlsLeftBorder = 8 xlsRightBorder = 16 xlsTopBorder = 32 xlsBottomBorder = 64 xlsShaded = 128 End Enum 'enum to handle selecting the font for the cell Public Enum CellFont 'used by rgbAttr2 'bits 0-5 handle the *picture* formatting, not bold/underline etc... 'bits 6-7 handle the font number xlsFont0 = 0 xlsFont1 = 64 xlsFont2 = 128 xlsFont3 = 192 End Enum Public Enum CellHiddenLocked 'used by rgbAttr1 'bits 0-5 must be zero 'bit 6 locked/unlocked 'bit 7 hidden/not hidden xlsNormal = 0 xlsLocked = 64 xlsHidden = 128 End Enum 'set up variables to hold the spreadsheet's layout Public Enum MarginTypes xlsLeftMargin = 38 xlsRightMargin = 39 xlsTopMargin = 40 xlsBottomMargin = 41 End Enum Public Enum FontFormatting 'add these enums together. For example: xlsBold + xlsUnderline xlsNoFormat = 0 xlsBold = 1 xlsItalic = 2 xlsUnderline = 4 xlsStrikeout = 8 End Enum Private Structure FONT_RECORD Dim opcode As Short '49 Dim length As Short '5+len(fontname) Dim FontHeight As Short 'bit0 bold, bit1 italic, bit2 underline, bit3 strikeout, bit4-7 reserved Dim FontAttributes1 As Byte Dim FontAttributes2 As Byte 'reserved - always 0 Dim FontNameLength As Byte End Structure Private Structure PASSWORD_RECORD Dim opcode As Short '47 Dim length As Short 'len(password) End Structure Private Structure HEADER_FOOTER_RECORD Dim opcode As Short '20 Header, 21 Footer Dim length As Short '1+len(text) Dim TextLength As Byte End Structure Private Structure PROTECT_SPREADSHEET_RECORD Dim opcode As Short '18 Dim length As Short '2 Dim Protect As Short End Structure Private Structure FORMAT_COUNT_RECORD Dim opcode As Short '1f Dim length As Short '2 Dim Count As Short End Structure Private Structure FORMAT_RECORD Dim opcode As Short '1e Dim length As Short '1+len(format) Dim FormatLenght As Byte 'len(format) End Structure '+ followed by the Format-Picture Private Structure COLWIDTH_RECORD Dim opcode As Short '36 Dim length As Short '4 Dim col1 As Byte 'first column Dim col2 As Byte 'last column Dim ColumnWidth As Short 'at 1/256th of a character End Structure 'Beginning Of File record Private Structure BEG_FILE_RECORD Dim opcode As Short Dim length As Short Dim version As Short Dim ftype As Short End Structure 'End Of File record Private Structure END_FILE_RECORD Dim opcode As Short Dim length As Short End Structure 'true/false to print gridlines Private Structure PRINT_GRIDLINES_RECORD Dim opcode As Short Dim length As Short Dim PrintFlag As Short End Structure 'Integer record Private Structure tInteger Dim opcode As Short Dim length As Short Dim row As Short 'unsigned integer Dim col As Short 'rgbAttr1 handles whether cell is hidden and/or locked Dim rgbAttr1 As Byte 'rgbAttr2 handles the Font# and Formatting assigned to this cell Dim rgbAttr2 As Byte 'rgbAttr3 handles the Cell Alignment/borders/shading Dim rgbAttr3 As Byte Dim intValue As Short 'the actual integer value End Structure 'Number record Private Structure tNumber Dim opcode As Short Dim length As Short Dim row As Short Dim col As Short Dim rgbAttr1 As Byte Dim rgbAttr2 As Byte Dim rgbAttr3 As Byte Dim NumberValue As Double '8 Bytes End Structure 'Label (Text) record Private Structure tText Dim opcode As Short Dim length As Short Dim row As Short Dim col As Short Dim rgbAttr1 As Byte Dim rgbAttr2 As Byte Dim rgbAttr3 As Byte Dim TextLength As Byte End Structure Private Structure MARGIN_RECORD_LAYOUT Dim opcode As Short Dim length As Short Dim MarginValue As Double '8 bytes End Structure Private Structure HPAGE_BREAK_RECORD Dim opcode As Short Dim length As Short Dim NumPageBreaks As Short End Structure Private Structure DEF_ROWHEIGHT_RECORD Dim opcode As Integer Dim length As Integer Dim RowHeight As Integer End Structure Private Structure ROW_HEIGHT_RECORD Dim opcode As Integer '08 Dim length As Integer 'should always be 16 bytes Dim RowNumber As Integer Dim FirstColumn As Integer Dim LastColumn As Integer Dim RowHeight As Integer 'written to file as 1/20ths of a point Dim internal As Integer Dim DefaultAttributes As Byte 'set to zero for no default attributes Dim FileOffset As Integer Dim rgbAttr1 As Byte Dim rgbAttr2 As Byte Dim rgbAttr3 As Byte End Structure 'the memory copy API is used in the MKI$ function which converts an integer 'value to a 2-byte string value to write to the file. (used by the Horizontal 'Page Break function). Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef lpvDest As String, ByRef lpvSource As Short, ByVal cbCopy As Integer) Private m_shtFileNumber As Short Private m_udtBEG_FILE_MARKER As BEG_FILE_RECORD Private m_udtEND_FILE_MARKER As END_FILE_RECORD Private m_udtHORIZ_PAGE_BREAK As HPAGE_BREAK_RECORD 'create an array that will hold the rows where a horizontal page 'break will be inserted just before. Private m_shtHorizPageBreakRows() As Short Private m_shtNumHorizPageBreaks As Short
[解决办法]
Public WriteOnly Property PrintGridLines() As Boolean Set(ByVal Value As Boolean) Try Dim GRIDLINES_RECORD As PRINT_GRIDLINES_RECORD With GRIDLINES_RECORD .opcode = 43 .length = 2 If Value = True Then .PrintFlag = 1 Else .PrintFlag = 0 End If End With FilePut(m_shtFileNumber, GRIDLINES_RECORD) Catch ex As Exception End Try End Set End Property Public WriteOnly Property ProtectSpreadsheet() As Boolean Set(ByVal Value As Boolean) Try Dim PROTECT_RECORD As PROTECT_SPREADSHEET_RECORD With PROTECT_RECORD .opcode = 18 .length = 2 If Value = True Then .Protect = 1 Else .Protect = 0 End If End With FilePut(m_shtFileNumber, PROTECT_RECORD) Catch ex As Exception End Try End Set End Property Public Function CreateFile(ByVal strFileName As String) As Integer Dim OpenFile As Integer Try If File.Exists(strFileName) Then File.SetAttributes(strFileName, FileAttributes.Normal) File.Delete(strFileName) End If m_shtFileNumber = FreeFile() FileOpen(m_shtFileNumber, strFileName, OpenMode.Binary) FilePut(m_shtFileNumber, m_udtBEG_FILE_MARKER) 'must always be written first Call WriteDefaultFormats() 'create the Horizontal Page Break array ReDim m_shtHorizPageBreakRows(0) m_shtNumHorizPageBreaks = 0 OpenFile = 0 'return with no error Catch ex As Exception OpenFile = Err.Number End Try End Function Public Function CloseFile() As Integer Dim x As Short Try If m_shtFileNumber > 0 Then 'write the horizontal page breaks if necessary Dim lLoop1 As Integer Dim lLoop2 As Integer Dim lTemp As Integer If m_shtNumHorizPageBreaks > 0 Then 'the Horizontal Page Break array must be in sorted order. 'Use a simple Bubble sort because the size of this array would 'be pretty small most of the time. A QuickSort would probably 'be overkill. For lLoop1 = UBound(m_shtHorizPageBreakRows) To LBound(m_shtHorizPageBreakRows) Step -1 For lLoop2 = LBound(m_shtHorizPageBreakRows) + 1 To lLoop1 If m_shtHorizPageBreakRows(lLoop2 - 1) > m_shtHorizPageBreakRows(lLoop2) Then lTemp = m_shtHorizPageBreakRows(lLoop2 - 1) m_shtHorizPageBreakRows(lLoop2 - 1) = m_shtHorizPageBreakRows(lLoop2) m_shtHorizPageBreakRows(lLoop2) = lTemp End If Next lLoop2 Next lLoop1 'write the Horizontal Page Break Record With m_udtHORIZ_PAGE_BREAK .opcode = 27 .length = 2 + (m_shtNumHorizPageBreaks * 2) .NumPageBreaks = m_shtNumHorizPageBreaks End With FilePut(m_shtFileNumber, m_udtHORIZ_PAGE_BREAK) 'now write the actual page break values 'the MKI$ function is standard in other versions of BASIC but 'VisualBasic does not have it. A KnowledgeBase article explains 'how to recreate it (albeit using 16-bit API, I switched it 'to 32-bit). For x = 1 To UBound(m_shtHorizPageBreakRows) FilePut(m_shtFileNumber, MKI(m_shtHorizPageBreakRows(x))) Next End If FilePut(m_shtFileNumber, m_udtEND_FILE_MARKER) FileClose(m_shtFileNumber) CloseFile = 0 'return with no error code Else CloseFile = -1 End If Catch ex As Exception CloseFile = Err.Number End Try End Function Private Sub Init() 'Set up default values for records 'These should be the values that are the same for every record of these types With m_udtBEG_FILE_MARKER 'beginning of file .opcode = 9 .length = 4 .version = 2 .ftype = 10 End With With m_udtEND_FILE_MARKER 'end of file marker .opcode = 10 End With End Sub
[解决办法]
Public Function SetFont(ByRef FontName As String, ByRef FontHeight As Short, ByRef FontFormat As FontFormatting) As Short Dim l As Short Try 'you can set up to 4 fonts in the spreadsheet file. When writing a value such 'as a Text or Number you can specify one of the 4 fonts (numbered 0 to 3) Dim FONTNAME_RECORD As FONT_RECORD l = GetLength(FontName) 'LenB(StrConv(FontName, vbFromUnicode)) 'Len(FontName) With FONTNAME_RECORD .opcode = 49 .length = 5 + l .FontHeight = FontHeight * 20 .FontAttributes1 = CByte(FontFormat) 'bold/underline etc... .FontAttributes2 = CByte(0) 'reserved-always zero!! .FontNameLength = CByte(l) 'CByte(Len(FontName)) End With FilePut(m_shtFileNumber, FONTNAME_RECORD) 'Then the actual font name data 'Dim b As Byte 'For a = 1 To l% ' b = Asc(Mid$(FontName, a, 1)) ' Put #m_shtFileNumber, , b 'Next FilePut(m_shtFileNumber, FontName) SetFont = 0 Catch ex As Exception SetFont = Err.Number End Try End Function Public Function SetHeader(ByRef HeaderText As String) As Integer Dim l As Short Try Dim HEADER_RECORD As HEADER_FOOTER_RECORD l = GetLength(HeaderText) 'LenB(StrConv(HeaderText, vbFromUnicode)) 'Len(HeaderText) With HEADER_RECORD .opcode = 20 .length = 1 + l .TextLength = CByte(l) 'CByte(Len(HeaderText)) End With FilePut(m_shtFileNumber, HEADER_RECORD) 'Then the actual Header text 'Dim b As Byte 'For a = 1 To l% ' b = Asc(Mid$(HeaderText, a, 1)) ' Put #m_shtFileNumber, , b 'Next FilePut(m_shtFileNumber, HeaderText) SetHeader = 0 Catch ex As Exception SetHeader = Err.Number End Try End Function Public Function SetFooter(ByRef FooterText As String) As Integer Dim l As Short Try Dim FOOTER_RECORD As HEADER_FOOTER_RECORD l = GetLength(FooterText) 'LenB(StrConv(FooterText, vbFromUnicode)) 'Len(FooterText) With FOOTER_RECORD .opcode = 21 .length = 1 + l .TextLength = CByte(l) 'CByte(Len(FooterText)) End With FilePut(m_shtFileNumber, FOOTER_RECORD) 'Then the actual Header text 'Dim b As Byte 'For a = 1 To l% ' b = Asc(Mid$(FooterText, a, 1)) ' Put #m_shtFileNumber, , b 'Next FilePut(m_shtFileNumber, FooterText) SetFooter = 0 Catch ex As Exception SetFooter = Err.Number End Try End Function Public Function SetFilePassword(ByRef PasswordText As String) As Integer Dim l As Short Try Dim FILE_PASSWORD_RECORD As PASSWORD_RECORD l = GetLength(PasswordText) 'LenB(StrConv(PasswordText, vbFromUnicode)) 'Len(PasswordText) With FILE_PASSWORD_RECORD .opcode = 47 .length = l End With FilePut(m_shtFileNumber, FILE_PASSWORD_RECORD) 'Then the actual Password text 'Dim b As Byte 'For a = 1 To l% ' b = Asc(Mid$(PasswordText, a, 1)) ' Put #m_shtFileNumber, , b 'Next FilePut(m_shtFileNumber, PasswordText) SetFilePassword = 0 Catch ex As Exception SetFilePassword = Err.Number End Try End Function
------解决方案--------------------
Private Function WriteDefaultFormats() As Integer Dim cFORMAT_COUNT_RECORD As FORMAT_COUNT_RECORD Dim cFORMAT_RECORD As FORMAT_RECORD Dim lIndex As Integer Dim aFormat(23) As String Dim l As Integer Dim q As String = Chr(34) aFormat(0) = "General" aFormat(1) = "0" aFormat(2) = "0.00" aFormat(3) = "#,##0" aFormat(4) = "#,##0.00" aFormat(5) = "#,##0\ " & q & "$" & q & ";\-#,##0\ " & q & "$" & q aFormat(6) = "#,##0\ " & q & "$" & q & ";[Red]\-#,##0\ " & q & "$" & q aFormat(7) = "#,##0.00\ " & q & "$" & q & ";\-#,##0.00\ " & q & "$" & q aFormat(8) = "#,##0.00\ " & q & "$" & q & ";[Red]\-#,##0.00\ " & q & "$" & q aFormat(9) = "0%" aFormat(10) = "0.00%" aFormat(11) = "0.00E+00" aFormat(12) = "dd/mm/yy" aFormat(13) = "dd/\ mmm\ yy" aFormat(14) = "dd/\ mmm" aFormat(15) = "mmm\ yy" aFormat(16) = "h:mm\ AM/PM" aFormat(17) = "h:mm:ss\ AM/PM" aFormat(18) = "hh:mm" aFormat(19) = "hh:mm:ss" aFormat(20) = "dd/mm/yy\ hh:mm" aFormat(21) = "##0.0E+0" aFormat(22) = "mm:ss" aFormat(23) = "@" With cFORMAT_COUNT_RECORD .opcode = &H1FS .length = &H2S .Count = CShort(UBound(aFormat)) End With FilePut(m_shtFileNumber, cFORMAT_COUNT_RECORD) Dim b As Byte Dim a As Integer For lIndex = LBound(aFormat) To UBound(aFormat) l = Len(aFormat(lIndex)) With cFORMAT_RECORD .opcode = &H1ES .length = CShort(l + 1) .FormatLenght = CShort(l) End With FilePut(m_shtFileNumber, cFORMAT_RECORD) 'Then the actual format For a = 1 To l b = Asc(Mid(aFormat(lIndex), a, 1)) FilePut(m_shtFileNumber, b) Next Next lIndex End Function Private Function MKI(ByRef x As Short) As String Dim temp As String 'used for writing integer array values to the disk file temp = Space(2) CopyMemory(temp, x, 2) MKI = temp End Function Private Function GetLength(ByVal strText As String) As Integer Return Encoding.Default.GetBytes(strText).Length End Function Public Function SetDefaultRowHeight(ByVal HeightValue As Integer) As Integer Try 'Height is defined in units of 1/20th of a point. Therefore, a 10-point font 'would be 200 (i.e. 200/20 = 10). This function takes a HeightValue such as '14 point and converts it the correct size before writing it to the file. Dim DEFHEIGHT As DEF_ROWHEIGHT_RECORD With DEFHEIGHT .opcode = 37 .length = 2 .RowHeight = HeightValue * 20 'convert points to 1/20ths of point End With FilePut(m_shtFileNumber, DEFHEIGHT) SetDefaultRowHeight = 0 Catch ex As Exception SetDefaultRowHeight = Err.Number End Try End Function Public Function SetRowHeight(ByVal Row As Integer, ByVal HeightValue As Short) As Integer Dim o_intRow As Integer Try 'the row and column values are written to the excel file as 'unsigned integers. Therefore, must convert the longs to integer. If Row > 32767 Then o_intRow = CInt(Row - 65536) Else o_intRow = CInt(Row) - 1 'rows/cols in Excel binary file are zero based End If 'Height is defined in units of 1/20th of a point. Therefore, a 10-point font 'would be 200 (i.e. 200/20 = 10). This function takes a HeightValue such as '14 point and converts it the correct size before writing it to the file. Dim ROWHEIGHTREC As ROW_HEIGHT_RECORD With ROWHEIGHTREC .opcode = 8 .length = 16 .RowNumber = o_intRow .FirstColumn = 0 .LastColumn = 256 .RowHeight = HeightValue * 20 'convert points to 1/20ths of point .internal = 0 .DefaultAttributes = 0 .FileOffset = 0 .rgbAttr1 = 0 .rgbAttr2 = 0 .rgbAttr3 = 0 End With FilePut(m_shtFileNumber, ROWHEIGHTREC) SetRowHeight = 0 Catch ex As Exception SetRowHeight = Err.Number End Try End Function
[解决办法]
我猜测，是利用读取二进制文件，然后分析文件格式，最后在流中加入数据。
[解决办法]
楼主精神可嘉，向您学习了。
[解决办法]
LZ很真诚啊...学习了~~
[解决办法]
[解决办法]
不懂 帮up
[解决办法]
联系人: 梁平丽（责任编辑）
投稿信箱:chinakjzh@126.com(注.中国科技纵横投稿）
编辑部电话：010--80955582（转683）
咨询电话: 13041201895
工作QQ： 909302841
《中国科技纵横》编辑部
[解决办法]
帮顶 学习下
[解决办法]
vb 很久没用。。帮顶。。。UP
[解决办法]
up
[解决办法]
帮顶，jf
[解决办法]
学习
[解决办法]
帮顶~~~
[解决办法]
Imports System.IOImports System.IO.IsolatedStorageImports System.ReflectionPublic Class test Private Sub test_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load End Sub Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Me.Close() End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click '读一个文本文件 If Me.OpenFileDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then Dim myfilename As String = Me.OpenFileDialog1.FileName Me.RichTextBox1.LoadFile(myfilename, RichTextBoxStreamType.PlainText) End If End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click '保存独立文件 If Me.RichTextBox1.Text.Length < 1 Then Return End If Try Dim myfile As New IsolatedStorageFileStream("zyt.zyt", FileMode.Create) Dim mywriter As New StreamWriter(myfile) mywriter.Write(Me.RichTextBox1.Text) mywriter.Flush() mywriter.Close() myfile.Close() Me.RichTextBox1.Text = "" MsgBox("保存成功") Catch ex As Exception MsgBox(ex.Message) End Try End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click '读取独立文件 Try Dim myfile As New IsolatedStorageFileStream("zyt.zyt", FileMode.Open) Dim myreader As New StreamReader(myfile) Me.RichTextBox1.Text = myreader.ReadToEnd() myreader.Close() myfile.Close() Catch ex As Exception MsgBox(ex.Message) End Try End Sub Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click Dim myobj As System.Object myobj = Missing.Value Dim myword As Microsoft.Office.Interop.Word.Application Dim mydoc As Microsoft.Office.Interop.Word.Document myword = New Microsoft.Office.Interop.Word.Application myword.Visible = True mydoc = myword.Documents.Open("zyt.zyt") mydoc.Select() End SubEnd Class
------解决方案--------------------
学习一下，帮忙顶了~~~还真的不是很清楚哦~~
[解决办法]
up
[解决办法]
如果有一次性大量类似操作，能因为直接从内存读取快的100毫秒而产生数分钟以上的差距，那么就用内存虚拟磁盘～～
fujiandragon 同学～IsolatedStorage不是说独立文件存在内存里，独立存储还是在磁盘上，在特定文件夹中，主要是为了安全性考虑，而加入的机制。只要注入silverligh或其他没有访问文件系统权限的应用也可以在客户端通过IsolatedStorage保存数据。 但这不是lz需要的。
[解决办法]
另外～lz，要不，你别用ADO.NET来操作数据了，该用ADO吧，Excel的COM对象直接有CopyFormRecordSet方法- -||
[解决办法]
挣分来的，无视我吧！
[解决办法]
帮顶 顺便接分
[解决办法]
C++ 可以用 BasicExcel
http://blog.csdn.net/DavidHsing/archive/2009/06/01/4231592.aspx
[解决办法]
楼主很有思想，继续观注学习中……