Excel文档生成类(C#版)

using System.Text; 
using System.IO;
using System;
namespace ExcelComm
{
    public class cExcelFile
    {
        public enum ValueTypes
        {
            xlsInteger = 0,
            xlsNumber = 1,
            xlsText = 2
        }
        public enum CellAlignment
        {
            xlsGeneralAlign = 0,
            xlsLeftAlign = 1,
            xlsCentreAlign = 2,
            xlsRightAlign = 3,
            xlsFillCell = 4,
            xlsLeftBorder = 8,
            xlsRightBorder = 16,
            xlsTopBorder = 32,
            xlsBottomBorder = 64,
            xlsShaded = 128
        }
        public enum CellFont
        {
            xlsFont0 = 0,
            xlsFont1 = 64,
            xlsFont2 = 128,
            xlsFont3 = 192
        }
        public enum CellHiddenLocked
        {
            xlsNormal = 0,
            xlsLocked = 64,
            xlsHidden = 128
        }
        public enum MarginTypes
        {
            xlsLeftMargin = 38,
            xlsRightMargin = 39,
            xlsTopMargin = 40,
            xlsBottomMargin = 41
        }
        public enum FontFormatting
        {
            xlsNoFormat = 0,
            xlsBold = 1,
            xlsItalic = 2,
            xlsUnderline = 4,
            xlsStrikeout = 8
        }
        public struct FONT_RECORD
        {
            public short opcode;
            public short length;
            public short FontHeight;
            public byte FontAttributes1;
            public byte FontAttributes2;
            public byte FontNameLength;
        }
        public struct PASSWORD_RECORD
        {
            public short opcode;
            public short length;
        }
        public struct HEADER_FOOTER_RECORD
        {
            public short opcode;
            public short length;
            public byte TextLength;
        }
        public struct PROTECT_SPREADSHEET_RECORD
        {
            public short opcode;
            public short length;
            public short Protect;
        }
        public struct FORMAT_COUNT_RECORD
        {
            public short opcode;
            public short length;
            public short Count;
        }
        public struct FORMAT_RECORD
        {
            public short opcode;
            public short length;
            public byte FormatLenght;
        }
        public struct COLWIDTH_RECORD
        {
            public short opcode;
            public short length;
            public byte col1;
            public byte col2;
            public short ColumnWidth;
        }
        public struct BEG_FILE_RECORD
        {
            public short opcode;
            public short length;
            public short version;
            public short ftype;
        }
        public struct END_FILE_RECORD
        {
            public short opcode;
            public short length;
        }
        public struct PRINT_GRIDLINES_RECORD
        {
            public short opcode;
            public short length;
            public short PrintFlag;
        }
        public struct tInteger
        {
            public short opcode;
            public short length;
            public short row;
            public short col;
            public byte rgbAttr1;
            public byte rgbAttr2;
            public byte rgbAttr3;
            public short intValue;
        }
        public struct tNumber
        {
            public short opcode;
            public short length;
            public short row;
            public short col;
            public byte rgbAttr1;
            public byte rgbAttr2;
            public byte rgbAttr3;
            public double NumberValue;
        }
        public struct tText
        {
            public short opcode;
            public short length;
            public short row;
            public short col;
            public byte rgbAttr1;
            public byte rgbAttr2;
            public byte rgbAttr3;
            public byte TextLength;
        }
        public struct MARGIN_RECORD_LAYOUT
        {
            public short opcode;
            public short length;
            public double MarginValue;
        }
        public struct HPAGE_BREAK_RECORD
        {
            public short opcode;
            public short length;
            public short NumPageBreaks;
        }
        public struct DEF_ROWHEIGHT_RECORD
        {
            public int opcode;
            public int length;
            public int RowHeight;
        }
        public struct ROW_HEIGHT_RECORD
        {
            public int opcode;
            public int length;
            public int RowNumber;
            public int FirstColumn;
            public int LastColumn;
            public int RowHeight;
            public int linternal;
            public byte DefaultAttributes;
            public int FileOffset;
            public byte rgbAttr1;
            public byte rgbAttr2;
            public byte rgbAttr3;
        }
        [System.Runtime.InteropServices.DllImport("kernel32", EntryPoint = "RtlMoveMemory")]
        private static extern void CopyMemory(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;
        private short[] m_shtHorizPageBreakRows;
        private short m_shtNumHorizPageBreaks;

        public void FilePut(short data)
        {
            byte[] value = BitConverter.GetBytes(data);
            fs.Write(value, 0, value.Length);
        }
        public void FilePut(byte data)
        {
            fs.WriteByte(data);
        }
        public void FilePut(int data)
        {
            byte[] value = BitConverter.GetBytes(data);
            fs.Write(value, 0, value.Length);
        }
        public void FilePut(double data)
        {
            byte[] value = BitConverter.GetBytes(data);
            fs.Write(value, 0, value.Length);
        }
        public void FilePut(string data)
        {
            byte[] value = System.Text.Encoding.Default.GetBytes(data);
            fs.Write(value, 0, value.Length);
        }
        public void FilePut(byte[] data)
        {
            fs.Write(data, 0, data.Length);
        }
        public bool PrintGridLines
        {
            set
            {
                try
                {
                    PRINT_GRIDLINES_RECORD GRIDLINES_RECORD;
                    GRIDLINES_RECORD.opcode = 43;
                    GRIDLINES_RECORD.length = 2;
                    if (value == true)
                    {
                        GRIDLINES_RECORD.PrintFlag = 1;
                    }
                    else
                    {
                        GRIDLINES_RECORD.PrintFlag = 0;
                    }
                    FilePut(GRIDLINES_RECORD.opcode);
                    FilePut(GRIDLINES_RECORD.length);
                    FilePut(GRIDLINES_RECORD.PrintFlag);
                }
                catch (Exception ex)
                {
                }
            }
        }

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

                }
                catch (Exception ex)
                {
                }
            }
        }

        public void CreateFile(string strFileName)
        {

            try
            {
                if (File.Exists(strFileName))
                {
                    File.SetAttributes(strFileName, FileAttributes.Normal);
                    File.Delete(strFileName);
                }
                //m_shtFileNumber = FreeFile(); 
                //FileOpen(m_shtFileNumber, strFileName, OpenMode.Binary);
                fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write);
                FilePut(m_udtBEG_FILE_MARKER.opcode);
                FilePut(m_udtBEG_FILE_MARKER.length);
                FilePut(m_udtBEG_FILE_MARKER.version);
                FilePut(m_udtBEG_FILE_MARKER.ftype);

                WriteDefaultFormats();
                m_shtHorizPageBreakRows = new short[1] { 0 };
                m_shtNumHorizPageBreaks = 0;

            }
            catch (Exception ex)
            {

            }
        }

        public void CloseFile()
        {
            try
            {
                short lTemp;
                if (m_shtNumHorizPageBreaks > 0)
                {
                    for (int lLoop1 = m_shtHorizPageBreakRows.GetUpperBound(0); lLoop1 >= m_shtHorizPageBreakRows.GetLowerBound(0); lLoop1--)
                    {
                        for (int 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] = lTemp;
                            }
                        }
                    }
                    m_udtHORIZ_PAGE_BREAK.opcode = 27;
                    m_udtHORIZ_PAGE_BREAK.length = (short)(2 + (m_shtNumHorizPageBreaks * 2));
                    m_udtHORIZ_PAGE_BREAK.NumPageBreaks = m_shtNumHorizPageBreaks;
                    FilePut(m_udtHORIZ_PAGE_BREAK.opcode);
                    FilePut(m_udtHORIZ_PAGE_BREAK.length);
                    FilePut(m_udtHORIZ_PAGE_BREAK.NumPageBreaks);

                    for (int x = 0; x < m_shtHorizPageBreakRows.GetUpperBound(0); x++)
                    {
                        FilePut(MKI(m_shtHorizPageBreakRows[x]));
                    }
                }
                FilePut(m_udtEND_FILE_MARKER.opcode);
                FilePut(m_udtEND_FILE_MARKER.length);
                fs.Close();

            }
            catch (Exception ex)
            {

            }
        }

        private void Init()
        {
            m_udtBEG_FILE_MARKER.opcode = 9;
            m_udtBEG_FILE_MARKER.length = 4;
            m_udtBEG_FILE_MARKER.version = 2;
            m_udtBEG_FILE_MARKER.ftype = 10;
            m_udtEND_FILE_MARKER.opcode = 10;
        }

        public cExcelFile()
        {
            //base.New();
            Init();
        }

        public void InsertHorizPageBreak(int lrow)
        {
            short row;
            
            
                if (lrow > 32767 || lrow < 0) row = 0;
                else row = (short)(lrow - 1);
                m_shtNumHorizPageBreaks = (short)(m_shtNumHorizPageBreaks + 1);
                short[] temp = m_shtHorizPageBreakRows;
                m_shtHorizPageBreakRows = new short[m_shtNumHorizPageBreaks + 1];
                int i = 0;
                while (i < temp.Length)
                {
                    m_shtHorizPageBreakRows[i] = temp[i];
                    i++;
                }
                m_shtHorizPageBreakRows[m_shtNumHorizPageBreaks] = row;
            
        }

        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)
        {
            short l;
            string st;
            short col;
            short row;
            try
            {
                tInteger INTEGER_RECORD;
                tNumber NUMBER_RECORD;
                byte b;
                tText TEXT_RECORD;
                if (lrow > 32767)
                {
                    row = System.Convert.ToInt16(lrow - 65536);
                }
                else
                {
                    row = (short)(lrow - (short)1);
                }
                if (lcol > 32767)
                {
                    col = System.Convert.ToInt16(lcol - 65536);
                }
                else
                {
                    col = (short)(lcol - (short)1);
                }
                if (ValueType == ValueTypes.xlsInteger)
                {
                    INTEGER_RECORD.opcode = 2;
                    INTEGER_RECORD.length = 9;
                    INTEGER_RECORD.row = row;
                    INTEGER_RECORD.col = col;
                    INTEGER_RECORD.rgbAttr1 = System.Convert.ToByte(HiddenLocked);
                    INTEGER_RECORD.rgbAttr2 = System.Convert.ToByte(CellFontUsed + CellFormat);
                    INTEGER_RECORD.rgbAttr3 = System.Convert.ToByte(Alignment);
                    INTEGER_RECORD.intValue = System.Convert.ToInt16(Value);
                    FilePut(INTEGER_RECORD.opcode);
                    FilePut(INTEGER_RECORD.length);
                    FilePut(INTEGER_RECORD.row);
                    FilePut(INTEGER_RECORD.col);
                    FilePut(INTEGER_RECORD.rgbAttr1);
                    FilePut(INTEGER_RECORD.rgbAttr2);
                    FilePut(INTEGER_RECORD.rgbAttr3);
                    FilePut(INTEGER_RECORD.intValue);
                }
                else if (ValueType == ValueTypes.xlsNumber)
                {
                    NUMBER_RECORD.opcode = 3;
                    NUMBER_RECORD.length = 15;
                    NUMBER_RECORD.row = row;
                    NUMBER_RECORD.col = col;
                    NUMBER_RECORD.rgbAttr1 = System.Convert.ToByte(HiddenLocked);
                    NUMBER_RECORD.rgbAttr2 = System.Convert.ToByte(CellFontUsed + CellFormat);
                    NUMBER_RECORD.rgbAttr3 = System.Convert.ToByte(Alignment);
                    NUMBER_RECORD.NumberValue = System.Convert.ToDouble(Value);
                    FilePut(NUMBER_RECORD.opcode);
                    FilePut(NUMBER_RECORD.length);
                    FilePut(NUMBER_RECORD.row);
                    FilePut(NUMBER_RECORD.col);
                    FilePut(NUMBER_RECORD.rgbAttr1);
                    FilePut(NUMBER_RECORD.rgbAttr2);
                    FilePut(NUMBER_RECORD.rgbAttr3);
                    FilePut(NUMBER_RECORD.NumberValue);
                }
                else if (ValueType == ValueTypes.xlsText)
                {
                    st = ((string)(Value));
                    l = (short)GetLength(st);
                    TEXT_RECORD.opcode = 4;
                    TEXT_RECORD.length = 10;
                    TEXT_RECORD.TextLength = (byte)l;
                    TEXT_RECORD.length = (short)(8 + l);
                    TEXT_RECORD.row = row;
                    TEXT_RECORD.col = col;
                    TEXT_RECORD.rgbAttr1 = System.Convert.ToByte(HiddenLocked);
                    TEXT_RECORD.rgbAttr2 = System.Convert.ToByte(CellFontUsed + CellFormat);
                    TEXT_RECORD.rgbAttr3 = System.Convert.ToByte(Alignment);
                    FilePut(TEXT_RECORD.opcode);
                    FilePut(TEXT_RECORD.length);
                    FilePut(TEXT_RECORD.row);
                    FilePut(TEXT_RECORD.col);
                    FilePut(TEXT_RECORD.rgbAttr1);
                    FilePut(TEXT_RECORD.rgbAttr2);
                    FilePut(TEXT_RECORD.rgbAttr3);
                    FilePut(TEXT_RECORD.TextLength);
                    FilePut(st);
                }

            }
            catch (Exception ex)
            {

            }
        }

        public void SetMargin(MarginTypes Margin, double MarginValue)
        {
            try
            {
                MARGIN_RECORD_LAYOUT MarginRecord;
                MarginRecord.opcode = (short)Margin;
                MarginRecord.length = 8;
                MarginRecord.MarginValue = MarginValue;
                FilePut(MarginRecord.opcode);
                FilePut(MarginRecord.length);
                FilePut(MarginRecord.MarginValue);

            }
            catch (Exception ex)
            {
            }
        }

        public void SetColumnWidth(byte FirstColumn, byte 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);
                FilePut(COLWIDTH.opcode);
                FilePut(COLWIDTH.length);
                FilePut(COLWIDTH.col1);
                FilePut(COLWIDTH.col2);
                FilePut(COLWIDTH.ColumnWidth);

            }
            catch (Exception ex)
            {

            }
        }

        public void SetFont(string FontName, short FontHeight, FontFormatting FontFormat)
        {
            short l;
            try
            {
                FONT_RECORD FONTNAME_RECORD;
                l = (short)GetLength(FontName);
                FONTNAME_RECORD.opcode = 49;
                FONTNAME_RECORD.length = (short)(5 + l);
                FONTNAME_RECORD.FontHeight = (short)(FontHeight * 20);
                FONTNAME_RECORD.FontAttributes1 = System.Convert.ToByte(FontFormat);
                FONTNAME_RECORD.FontAttributes2 = System.Convert.ToByte(0);
                FONTNAME_RECORD.FontNameLength = System.Convert.ToByte(l);
                FilePut(FONTNAME_RECORD.opcode);
                FilePut(FONTNAME_RECORD.length);
                FilePut(FONTNAME_RECORD.FontHeight);
                FilePut(FONTNAME_RECORD.FontAttributes1);
                FilePut(FONTNAME_RECORD.FontAttributes2);
                FilePut(FONTNAME_RECORD.FontNameLength);
                FilePut(FontName);
            }
            catch
            {
            }
        }

        public void SetHeader(string HeaderText)
        {
            short l;
            try
            {
                HEADER_FOOTER_RECORD HEADER_RECORD;
                l = (short)GetLength(HeaderText);
                HEADER_RECORD.opcode = 20;
                HEADER_RECORD.length = (short)(1 + l);
                HEADER_RECORD.TextLength = System.Convert.ToByte(l);

                FilePut(HEADER_RECORD.opcode);
                FilePut(HEADER_RECORD.length);
                FilePut(HEADER_RECORD.TextLength);
                FilePut(HeaderText);

            }
            catch
            {

            }
        }

        public void SetFooter(string FooterText)
        {
            short l;
            try
            {
                HEADER_FOOTER_RECORD FOOTER_RECORD;
                l = (short)GetLength(FooterText);
                FOOTER_RECORD.opcode = 21;
                FOOTER_RECORD.length = (short)(1 + l);
                FOOTER_RECORD.TextLength = System.Convert.ToByte(l);

                FilePut(FOOTER_RECORD.opcode);
                FilePut(FOOTER_RECORD.length);
                FilePut(FOOTER_RECORD.TextLength);
                FilePut(FooterText);

            }
            catch (Exception ex)
            {

            }
        }

        public void SetFilePassword(string PasswordText)
        {
            short l;
            try
            {
                PASSWORD_RECORD FILE_PASSWORD_RECORD;
                l = (short)GetLength(PasswordText);
                FILE_PASSWORD_RECORD.opcode = 47;
                FILE_PASSWORD_RECORD.length = l;
                FilePut(FILE_PASSWORD_RECORD.opcode);
                FilePut(FILE_PASSWORD_RECORD.length);
                FilePut(PasswordText);

            }
            catch (Exception ex)
            {
            }
        }

        private void WriteDefaultFormats()
        {
            FORMAT_COUNT_RECORD cFORMAT_COUNT_RECORD;
            FORMAT_RECORD cFORMAT_RECORD;

            string[] aFormat = new string[24];
            int l;
            string q = "/"";
            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] = "@";
            cFORMAT_COUNT_RECORD.opcode = 31;
            cFORMAT_COUNT_RECORD.length = 2;
            cFORMAT_COUNT_RECORD.Count = System.Convert.ToInt16(aFormat.GetUpperBound(0));
            FilePut(cFORMAT_COUNT_RECORD.opcode);
            FilePut(cFORMAT_COUNT_RECORD.length);
            FilePut(cFORMAT_COUNT_RECORD.Count);

            for (int lIndex = aFormat.GetLowerBound(0); lIndex <= aFormat.GetUpperBound(0); lIndex++)
            {
                l = aFormat[lIndex].Length;
                cFORMAT_RECORD.opcode = 30;
                cFORMAT_RECORD.length = System.Convert.ToInt16(l + 1);
                cFORMAT_RECORD.FormatLenght = (byte)l;
                FilePut(cFORMAT_RECORD.opcode);
                FilePut(cFORMAT_RECORD.length);
                FilePut(cFORMAT_RECORD.FormatLenght);
                //for (int a = 1; a <= l; a++)
                //{
                //    int b = Convert.ToInt32(aFormat[lIndex].Substring(a, 1));
                //    //Mid(aFormat[lIndex], a, 1);
                //    FilePut(aFormat[lIndex]);
                //}
                FilePut(aFormat[lIndex]);
            }
        }

        private byte[] MKI(short x)
        {
            //string temp;
            //temp = Space(2);
            //CopyMemory(temp, x, 2);
            //MKI = temp;
            return BitConverter.GetBytes(x);
        }

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

        public void SetDefaultRowHeight(int HeightValue)
        {
            try
            {
                DEF_ROWHEIGHT_RECORD DEFHEIGHT;
                DEFHEIGHT.opcode = 37;
                DEFHEIGHT.length = 2;
                DEFHEIGHT.RowHeight = HeightValue * 20;
                FilePut(DEFHEIGHT.opcode);
                FilePut(DEFHEIGHT.length);
                FilePut(DEFHEIGHT.RowHeight);

            }
            catch
            {

            }
        }

        public void SetRowHeight(int Row, short HeightValue)
        {
            int o_intRow;
            try
            {
                if (Row > 32767)
                {
                    o_intRow = System.Convert.ToInt32(Row - 65536);
                }
                else
                {
                    o_intRow = System.Convert.ToInt32(Row) - 1;
                }
                ROW_HEIGHT_RECORD ROWHEIGHTREC;
                ROWHEIGHTREC.opcode = 8;
                ROWHEIGHTREC.length = 16;
                ROWHEIGHTREC.RowNumber = o_intRow;
                ROWHEIGHTREC.FirstColumn = 0;
                ROWHEIGHTREC.LastColumn = 256;
                ROWHEIGHTREC.RowHeight = HeightValue * 20;
                ROWHEIGHTREC.linternal = 0;
                ROWHEIGHTREC.DefaultAttributes = 0;
                ROWHEIGHTREC.FileOffset = 0;
                ROWHEIGHTREC.rgbAttr1 = 0;
                ROWHEIGHTREC.rgbAttr2 = 0;
                ROWHEIGHTREC.rgbAttr3 = 0;

                FilePut(ROWHEIGHTREC.opcode);
                FilePut(ROWHEIGHTREC.length);
                FilePut(ROWHEIGHTREC.RowNumber);
                FilePut(ROWHEIGHTREC.FirstColumn);
                FilePut(ROWHEIGHTREC.LastColumn);
                FilePut(ROWHEIGHTREC.RowHeight);
                FilePut(ROWHEIGHTREC.linternal);
                FilePut(ROWHEIGHTREC.DefaultAttributes);
                FilePut(ROWHEIGHTREC.FileOffset);
                FilePut(ROWHEIGHTREC.rgbAttr1);
                FilePut(ROWHEIGHTREC.rgbAttr2);
                FilePut(ROWHEIGHTREC.rgbAttr3);

            }
            catch
            {

            }
        }
    }

}