VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > VB.net教程 >
  • 与灵感之源的vb.net对应的SmartExcel的C#版本

我的代码,是从sf.net上down下来的vb.net的版本。该版本已经很久没有进行维护了。我将其转化到了C#版本。不过说实话,没有一个WYSWYG的ide(如excel的designer),这段代码没什么作用。写一个excel文件,太复杂了。。。

下面是SmartExcel.CS的代码,其他的代码,我都放在回复里面,请慢慢看。。。
using System;
using System.IO;
using System.Runtime.InteropServices;
using System.Reflection;

namespace Genersoft.Platform.Application.SmartExcel
{
    
/// <summary>
    /// Excel读写类
    
/// </summary>

    public class SmartExcel
    
{
        
//        '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)
        
//
        [DllImport("kernel32.dll")]
        
private static extern void RtlMoveMemory(ref string lpvDest,ref short lpvSource,int cbCopy);
        
private FileStream fs;
        
private BEG_FILE_RECORD m_udtBEG_FILE_MARKER;
        
private END_FILE_RECORD m_udtEND_FILE_MARKER;
        
private HPAGE_BREAK_RECORD m_udtHORIZ_PAGE_BREAK;

                                                                                                                                        
        
//create an array that will hold the rows where a horizontal page break will be inserted just before.
        private int[] m_shtHorizPageBreakRows;
        
private int m_shtNumHorizPageBreaks=1;
        
        
private void FilePut(byte[] buf)
        
{
            fs.Write(buf,
0,buf.Length);
        }


        
private void FilePut(System.ValueType vt)
        
{            
            Type t 
= vt.GetType();
            
int size = 0;

//            foreach(FieldInfo fi in t.GetFields())
//            {
//                size += Marshal.SizeOf(fi.FieldType);
//                
//                System.Diagnostics.Trace.WriteLine(fi.Name);
//            }
            
            size 
= Marshal.SizeOf(vt);
            IntPtr p 
= Marshal.AllocHGlobal(size);
            Marshal.StructureToPtr(vt,p,
true);
            
byte[] buf = new byte[size];
            Marshal.Copy(p,buf,
0,size);            

            fs.Write(buf,
0,buf.Length);
            Marshal.FreeHGlobal(p);
        }


        
private void FilePut(System.ValueType vt,int len)
        
{            
            
int size = 0;
            size 
= len;
            IntPtr p 
= Marshal.AllocHGlobal(size);
            Marshal.StructureToPtr(vt,p,
true);
            
byte[] buf = new byte[size];
            Marshal.Copy(p,buf,
0,size);            

            fs.Write(buf,
0,buf.Length);
            Marshal.FreeHGlobal(p);
        }


        
public bool PrintGridLines
        
{
            
set
            {
                
try
                {
                    PRINT_GRIDLINES_RECORD GRIDLINES_RECORD;
        
                    GRIDLINES_RECORD.opcode 
= 43;
                    GRIDLINES_RECORD.length 
= 2;
                    
if(true == value)
                    
{
                        GRIDLINES_RECORD.PrintFlag 
= 1;    
                    }

                    else
                    {
                        GRIDLINES_RECORD.PrintFlag 
= 0;    
                    }

                    FilePut(GRIDLINES_RECORD);
                }

                catch(Exception ex)
                
{
                    
throw ex;
                }
                                                                                                                                        
            }

        }

        
        
public bool ProtectSpreadsheet
        
{
            
set
            {
                
try
                {
                    PROTECT_SPREADSHEET_RECORD PROTECT_RECORD;
        
                                           
                    PROTECT_RECORD.opcode 
= 18;
                    PROTECT_RECORD.length 
= 2;
                    
if(true == value)
                    
{
                        PROTECT_RECORD.Protect 
= 1;
                    }

                    else
                    {
                        PROTECT_RECORD.Protect 
= 0;
                    }

                    
                    
if(null == fs)throw new SmartExcelOpeartionFileException();
                    FilePut(PROTECT_RECORD);
                }

                catch(Exception ex)
                
{
                    
throw ex;    
                }

            }

        }

        //
        public void CreateFile(string strFileName)
        
{
            
try
            {
                
if(File.Exists(strFileName))
                
{
                    File.SetAttributes(strFileName, FileAttributes.Normal);
                    File.Delete(strFileName);
                }

        
                fs 
= new FileStream(strFileName,FileMode.CreateNew);
                FilePut(m_udtBEG_FILE_MARKER);
        
                WriteDefaultFormats();
                
//                    'create the Horizontal Page Break array
                m_shtHorizPageBreakRows = new int[1]{0};
                m_shtNumHorizPageBreaks 
= 0;
            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }

        //
        public void CloseFile()
        
{
            
short x;
            
try
            {
                
if(null != fs)
                
{
                    
//'write the horizontal page breaks if necessary
                    int lLoop1,lLoop2,lTemp;

                    
if(m_shtNumHorizPageBreaks > 0)
                    
{
                        
//                                               '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=m_shtHorizPageBreakRows.GetUpperBound(0);lLoop1>=m_shtHorizPageBreakRows.GetLowerBound(0);lLoop1--)
                        
{
                            
for(lLoop2=m_shtHorizPageBreakRows.GetLowerBound(0)+1;lLoop2<=lLoop1;lLoop2++)
                            
{
                                
if(m_shtHorizPageBreakRows[lLoop2 - 1> m_shtHorizPageBreakRows[lLoop2])
                                
{
                                    lTemp 
= m_shtHorizPageBreakRows[lLoop2 - 1];
                                    m_shtHorizPageBreakRows[lLoop2 
- 1= m_shtHorizPageBreakRows[lLoop2];
                                    m_shtHorizPageBreakRows[lLoop2] 
= (short)lTemp;
                                }

                            }

                        }

                        //'write the Horizontal Page Break Record
                        m_udtHORIZ_PAGE_BREAK.opcode = 27;
                        m_udtHORIZ_PAGE_BREAK.length 
= (short)(2 + (m_shtNumHorizPageBreaks * 2));
                        m_udtHORIZ_PAGE_BREAK.NumPageBreaks 
= (short)m_shtNumHorizPageBreaks;
                                     
                        FilePut(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;x<=m_shtHorizPageBreakRows.GetUpperBound(0);x++)
                        
{
                            FilePut(System.Text.Encoding.Default.GetBytes(MKI((
short)(m_shtHorizPageBreakRows[x]))));
                        }

                    }

                    FilePut(m_udtEND_FILE_MARKER);
                    fs.Close();
                }

            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }

        
        
private void Init()
        
{
            
//                                                                         'Set up default values for records
            
//        'These should be the values that are the same for every record of these types
        
            
// beginning of file
            m_udtBEG_FILE_MARKER.opcode = 9;
            m_udtBEG_FILE_MARKER.length 
= 4;
            m_udtBEG_FILE_MARKER.version 
= 2;
            m_udtBEG_FILE_MARKER.ftype 
= 10;

            
// end of file marker
            m_udtEND_FILE_MARKER.opcode = 10;            
        }


        
public SmartExcel()
        
{
            Init();
        }


        
        
public void InsertHorizPageBreak(int lrow)
        
{
            
int row;
            
try
            {
                
//    'the row and column values are written to the excel file as
                
//    'unsigned integers. Therefore, must convert the longs to integer.
                if(lrow>32767 || lrow<0)row = 0;
                
else row = lrow-1;
                m_shtNumHorizPageBreaks 
= m_shtNumHorizPageBreaks + 1;
                m_shtHorizPageBreakRows[m_shtNumHorizPageBreaks] 
= row;
            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }
        
        
        
public void WriteValue(ValueTypes ValueType , CellFont CellFontUsed, CellAlignment Alignment, CellHiddenLocked HiddenLocked , int lrow, int lcol, object Value)
        
{
            WriteValue(ValueType , CellFontUsed, Alignment, HiddenLocked , lrow, lcol, Value,
0);
        }


        
public void WriteValue(ValueTypes ValueType , CellFont CellFontUsed, CellAlignment Alignment, CellHiddenLocked HiddenLocked , int lrow, int lcol, object Value,int CellFormat)
        
{
            
int l;
            
string st;
            
short col,row;
            
try
            {
                
//                            'the row and column values are written to the excel file as
                
//                'unsigned integers. Therefore, must convert the longs to integer.
                tInteger INTEGER_RECORD;
                tNumber NUMBER_RECORD;
                
byte b;
                tText TEXT_RECORD;
                
if(lrow>32767 || lrow<0)row = 0;
                
else row = (short)(lrow-1);
                
if(lcol > 32767 || lcol<0)col = 0;else col = (short)(lcol - 1);
                
switch(ValueType)
                
{
                    
case ValueTypes.Integer:
                        INTEGER_RECORD.opcode 
= 2;
                        INTEGER_RECORD.length 
= 9;
                        INTEGER_RECORD.row 
= row;
                        INTEGER_RECORD.col 
= col;
                        INTEGER_RECORD.rgbAttr1 
= (byte)(HiddenLocked);
                        INTEGER_RECORD.rgbAttr2 
= (byte)(CellFontUsed + CellFormat);
                        INTEGER_RECORD.rgbAttr3 
= (byte)(Alignment);
                        INTEGER_RECORD.intValue 
= (short)(Value);
                        FilePut(INTEGER_RECORD);
                        
break;
                    
case ValueTypes.Number:
                        NUMBER_RECORD.opcode 
= 3;
                        NUMBER_RECORD.length 
= 15;
                        NUMBER_RECORD.row 
= row;
                        NUMBER_RECORD.col 
= col;
                        NUMBER_RECORD.rgbAttr1 
= (byte)(HiddenLocked);
                        NUMBER_RECORD.rgbAttr2 
= (byte)(CellFontUsed + CellFormat);
                        NUMBER_RECORD.rgbAttr3 
= (byte)(Alignment);
                        NUMBER_RECORD.NumberValue 
= (double)(Value);
                        FilePut(NUMBER_RECORD);
                        
break;
                    
case ValueTypes.Text:
                        st 
= Convert.ToString(Value);
                        l 
= GetLength(st);// 'LenB(StrConv(st, vbFromUnicode)) 'Len(st$)
                    
                        TEXT_RECORD.opcode 
= 4;
                        TEXT_RECORD.length 
= 10;
                        
//'Length of the text portion of the record
                        TEXT_RECORD.TextLength = (byte)l;
                        
//                          'Total length of the record
                        TEXT_RECORD.length = (byte)(8 + l);
                        TEXT_RECORD.row 
= row;
                        TEXT_RECORD.col 
= col;
                        TEXT_RECORD.rgbAttr1 
= (byte)(HiddenLocked);
                        TEXT_RECORD.rgbAttr2 
= (byte)(CellFontUsed + CellFormat);
                        TEXT_RECORD.rgbAttr3 
= (byte)(Alignment);
                        
//                        'Put record header
                        FilePut(TEXT_RECORD);
                        
//                'Then the actual string data
                        FilePut(System.Text.Encoding.Default.GetBytes(st));
                        
break;
                    
default:break;
                }

            }

            catch(Exception ex)
            
{
                
throw ex;
            }
        
        }

        
        
public void SetMargin(MarginTypes Margin , double MarginValue)
        
{
            
try
            {
                
//                      'write the spreadsheet's layout information (in inches)
                MARGIN_RECORD_LAYOUT MarginRecord;
                MarginRecord.opcode 
= (short)Margin;
                MarginRecord.length 
= 8;
                MarginRecord.MarginValue 
= MarginValue;// 'in inches
                FilePut(MarginRecord);
            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }

        
        
public void SetColumnWidth(int FirstColumn, int LastColumn, short WidthValue)
        
{
            
try
            {
                COLWIDTH_RECORD COLWIDTH;
                            
                COLWIDTH.opcode 
= 36;
                COLWIDTH.length 
= 4;
                COLWIDTH.col1 
= (byte)(FirstColumn - 1);
                COLWIDTH.col2 
= (byte)(LastColumn - 1);
                COLWIDTH.ColumnWidth 
= (short)(WidthValue * 256);// 'values are specified as 1/256 of a character
                FilePut(COLWIDTH);
            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }

        
        
public void SetFont(string FontName, short FontHeight, FontFormatting FontFormat)
        
{
            
int l;
            
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)
                FONT_RECORD FONTNAME_RECORD;
                l 
= GetLength(FontName);// 'LenB(StrConv(FontName, vbFromUnicode)) 'Len(FontName)
                FONTNAME_RECORD.opcode = 49;
                FONTNAME_RECORD.length 
= (short)(5 + l);
                FONTNAME_RECORD.FontHeight 
= (short)(FontHeight * 20);
                FONTNAME_RECORD.FontAttributes1 
= (byte)FontFormat;// 'bold/underline etc
                FONTNAME_RECORD.FontAttributes2 = (byte)0;// 'reserved-always zero!!
                FONTNAME_RECORD.FontNameLength = (byte)l;//'CByte(Len(FontName))
                FilePut(FONTNAME_RECORD);
                
//                        'Then the actual font name data
                FilePut(System.Text.Encoding.Default.GetBytes(FontName));
            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }

        
        
public void SetHeader(string HeaderText)
        
{
            
int l;
            
try
            {
                HEADER_FOOTER_RECORD HEADER_RECORD;
                l 
= GetLength(HeaderText);//   'LenB(StrConv(HeaderText, vbFromUnicode)) 'Len(HeaderText)
                HEADER_RECORD.opcode = 20;
                HEADER_RECORD.length 
= (short)(1 + l);
                HEADER_RECORD.TextLength 
= (byte)l;// 'CByte(Len(HeaderText))
                FilePut(HEADER_RECORD);
                
//                        'Then the actual Header text
                FilePut(System.Text.Encoding.Default.GetBytes(HeaderText));
            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }

        
        
public void SetFooter(string FooterText)
        
{
            
int l;
            
try
            {
                HEADER_FOOTER_RECORD FOOTER_RECORD;
                l 
= GetLength(FooterText);// 'LenB(StrConv(FooterText, vbFromUnicode)) 'Len(FooterText)
                FOOTER_RECORD.opcode = 21;
                FOOTER_RECORD.length 
= (short)(1 + l);
                FOOTER_RECORD.TextLength 
= (byte)l;
                FilePut(FOOTER_RECORD);
                
//                    'Then the actual Header text
                FilePut(System.Text.Encoding.Default.GetBytes(FooterText));
            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }

        public void SetFilePassword(string PasswordText)
        
{
            
int l;
            
try
            {
                PASSWORD_RECORD FILE_PASSWORD_RECORD;
                l 
= GetLength(PasswordText);// 'LenB(StrConv(PasswordText, vbFromUnicode)) 'Len(PasswordText)
                FILE_PASSWORD_RECORD.opcode = 47;
                FILE_PASSWORD_RECORD.length 
= (short)l;
                FilePut(FILE_PASSWORD_RECORD);
                
//          'Then the actual Password text
                FilePut(System.Text.Encoding.Default.GetBytes(PasswordText));
            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }

        
        
private void WriteDefaultFormats()
        
{
            FORMAT_COUNT_RECORD cFORMAT_COUNT_RECORD;
            FORMAT_RECORD cFORMAT_RECORD;
            
int lIndex;
            
int l;
            
string q = """;
            
string[] aFormat = new string[]{
                                               
"General",
                                               
"0",
                                               
"0.00",
                                               
"#,##0",
                                               
"#,##0.00",
                                               
"#,##0\ "+q+"$"+q+";\-#,##0\ "+q+"$"+q,
                                               
"#,##0\ "+q+"$"+q+";[Red]\-#,##0\ "+q+"$"+q,
                                               
"#,##0.00\ "+q+"$"+q+";\-#,##0.00\ "+q+"$"+q,
                                               
"#,##0.00\ "+q+"$"+q+";[Red]\-#,##0.00\ "+q+"$"+q,
                                               
"0%",
                                               
"0.00%",
                                               
"0.00E+00",
                                               
"dd/mm/yy",
                                               
"dd/\ mmm\ yy",
                                               
"dd/\ mmm",
                                               
"mmm\ yy",
                                               
"h:mm\ AM/PM",
                                               
"h:mm:ss\ AM/PM",
                                               
"hh:mm",
                                               
"hh:mm:ss",
                                               
"dd/mm/yy\ hh:mm",
                                               
"##0.0E+0",
                                               
"mm:ss",
                                               
"@"}
;
        
                
            cFORMAT_COUNT_RECORD.opcode 
= 0x1f;
            cFORMAT_COUNT_RECORD.length 
= 0x02;
            cFORMAT_COUNT_RECORD.Count 
= (short)(aFormat.GetUpperBound(0));
            FilePut(cFORMAT_COUNT_RECORD);
        
            
byte b;
            
int a;
            
for(lIndex = aFormat.GetLowerBound(0);lIndex<=aFormat.GetUpperBound(0);lIndex++)
            
{
                l 
= aFormat[lIndex].Length;
                cFORMAT_RECORD.opcode 
= 0x1e;
                cFORMAT_RECORD.length 
= (short)(l+1);
                cFORMAT_RECORD.FormatLength 
= (byte)(l);
                FilePut(cFORMAT_RECORD);
                
//                Then the actual format
                
// 从1开始还是从0开始?!
                for(a=0;a<l;a++)
                
{
                    b 
= (byte)(aFormat[lIndex].Substring(a,1).ToCharArray(0,1)[0]);
                    FilePut(
new byte[]{b});
                }

            }

        }

        
        
private string MKI(short x)
        
{
            
string temp;
            
//'used for writing integer array values to the disk file
            temp = "  ";
            RtlMoveMemory(
ref temp, ref x, 2);
            
return temp;
        }

        
        
private int GetLength(string strText)
        
{
            
return System.Text.Encoding.Default.GetBytes(strText).Length;
        }

        
        
public void SetDefaultRowHeight(int HeightValue)
        
{
            
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.
        
                DEF_ROWHEIGHT_RECORD DEFHEIGHT;
                DEFHEIGHT.opcode 
= 37;
                DEFHEIGHT.length 
= 2;
                DEFHEIGHT.RowHeight 
= HeightValue * 20;//  'convert points to 1/20ths of point
                FilePut(DEFHEIGHT);
            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }

        
        
public void SetRowHeight(int Row,short HeightValue)
        
{
            
int o_intRow;
            
//               'the row and column values are written to the excel file as
            
//                'unsigned integers. Therefore, must convert the longs to integer.
            if(Row > 32767)throw new Exception("行号不能大于32767!");
            
try
            {
                o_intRow 
= Row;
                
//                if(Row > 32767){
                
//                                   o_intRow = CInt(Row - 65536)
                
//                                   Else
                
//                                       o_intRow = CInt(Row) - 1    'rows/cols in Excel binary file are zero based
                
//                                                                   }

                
//                    '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.
        
                ROW_HEIGHT_RECORD ROWHEIGHTREC;
                ROWHEIGHTREC.opcode 
= 8;
                ROWHEIGHTREC.length 
= 16;
                ROWHEIGHTREC.RowNumber 
= o_intRow;
                ROWHEIGHTREC.FirstColumn 
= 0;
                ROWHEIGHTREC.LastColumn 
= 256;
                ROWHEIGHTREC.RowHeight 
= HeightValue * 20;// 'convert points to 1/20ths of point
                ROWHEIGHTREC.internals = 0;
                ROWHEIGHTREC.DefaultAttributes 
= 0;
                ROWHEIGHTREC.FileOffset 
= 0;
                ROWHEIGHTREC.rgbAttr1 
= 0;
                ROWHEIGHTREC.rgbAttr2 
= 0;
                ROWHEIGHTREC.rgbAttr3 
= 0;
                FilePut(ROWHEIGHTREC);
            }

            catch(Exception ex)
            
{
                
throw ex;
            }

        }

    }

}
 
 


相关教程