我的代码，是从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;
}
}
}
}
下面是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; } } }}
Feedback
using System;
namespace Genersoft.Platform.Application.SmartExcel
{
/// <summary>
/// 所有的枚举类型定义
/// </summary>
public enum ValueTypes
{
Integer = 0,
Number = 1,
Text = 2,
}
public enum CellAlignment
{
GeneralAlign = 0,
LeftAlign = 1,
CentreAlign = 2,
RightAlign = 3,
FillCell = 4,
LeftBorder = 8,
RightBorder = 16,
TopBorder = 32,
BottomBorder = 64,
Shaded = 128
}
// 'used by rgbAttr2
//'bits 0-5 handle the *picture* formatting, not bold/underline etc...
//'bits 6-7 handle the font number
public enum CellFont
{
Font0 = 0,
Font1 = 64,
Font2 = 128,
Font3 = 192,
}
// 'used by rgbAttr1
// 'bits 0-5 must be zero
// 'bit 6 locked/unlocked
// 'bit 7 hidden/not hidden
public enum CellHiddenLocked
{
Normal = 0,
Locked = 64,
Hidden = 128,
}
public enum MarginTypes
{
LeftMargin = 38,
RightMargin = 39,
TopMargin = 40,
BottomMargin = 41,
}
/// <summary>
///
/// </summary>
/// <remarks>可以使用|符号进行或组合</remarks>
public enum FontFormatting
{
NoFormat = 0,
Bold = 1,
Italic = 2,
Underline = 4,
Strikeout = 8
}
}
回复 更多评论
using System;
namespace Genersoft.Platform.Application
{
public class SmartExcelOpeartionFileException : ApplicationException
{
public SmartExcelOpeartionFileException() : base("请首先调用CreateFile方法!")
{
}
}
}
回复 更多评论
using System;
using System.Runtime.InteropServices;
namespace Genersoft.Platform.Application
{
/// <summary>
/// 读写EXCEL文件所需的所有结构体的定义
/// </summary>
///
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct FONT_RECORD
{
public short opcode;//49
public short length;//5+len(fontname)
public short FontHeight;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte FontAttributes1;//bit0 bold, bit1 italic, bit2 underline, bit3 strikeout, bit4-7 reserved
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte FontAttributes2;//reserved - always 0
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte FontNameLength;
}
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct PASSWORD_RECORD
{
public short opcode;//47
public short length;//len(password)
}
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct HEADER_FOOTER_RECORD
{
public short opcode;//20 Header, 21 Footer
public short length;//1+len(text)
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte TextLength;
}
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct PROTECT_SPREADSHEET_RECORD
{
public short opcode;//18
public short length;//2
public short Protect;
}
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct FORMAT_COUNT_RECORD
{
public short opcode;//0x1f
public short length;//2
public short Count;
}
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct FORMAT_RECORD
{
public short opcode;// 0x1e
public short length;//1+len(format)
[MarshalAs(UnmanagedType.U1, SizeConst=1)]public byte FormatLength;//len(format)
}//followed by the Format-Picture
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct COLWIDTH_RECORD
{
public short opcode;//36
public short length;//4
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte col1;//first column
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte col2;//last column
public short ColumnWidth;//at 1/256th of a character
}
// 'Beginning Of File record
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct BEG_FILE_RECORD
{
public short opcode;
public short length;
public short version;
public short ftype;
}
// 'End Of File record
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct END_FILE_RECORD
{
public short opcode;
public short length;
}
// 'true/false to print gridlines
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct PRINT_GRIDLINES_RECORD
{
public short opcode;
public short length;
public short PrintFlag;
}
// 'Integer record
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct tInteger
{
public short opcode;
public short length;
public short row;//unsigned integer
public short col;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr1;//rgbAttr1 handles whether cell is hidden and/or locked
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr2;//rgbAttr2 handles the Font# and Formatting assigned to this cell
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr3;//rgbAttr3 handles the Cell Alignment/borders/shading
public short intValue;//the actual integer value
}
// 'Number record
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct tNumber
{
public short opcode;
public short length;
public short row;
public short col;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr1;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr2;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr3;
public double NumberValue;//8 Bytes
}
//
// 'Label (Text) record
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct tText
{
public short opcode;
public short length;
public short row;
public short col;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr1;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr2;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr3;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte TextLength;
}
//
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct MARGIN_RECORD_LAYOUT
{
public short opcode;
public short length;
public double MarginValue;//8 bytes
}
//
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct HPAGE_BREAK_RECORD
{
public short opcode;
public short length;
public short NumPageBreaks;
}
//
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct DEF_ROWHEIGHT_RECORD
{
public int opcode;
public int length;
public int RowHeight;
}
//
[StructLayoutAttribute(LayoutKind.Sequential, CharSet=CharSet.Auto, Pack=1)]
struct ROW_HEIGHT_RECORD
{
public int opcode;//08
public int length;//should always be 16 bytes
public int RowNumber;
public int FirstColumn;
public int LastColumn;
public int RowHeight;//written to file as 1/20ths of a point
public int internals;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte DefaultAttributes;//set to zero for no default attributes
public int FileOffset;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr1;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr2;
[MarshalAs(UnmanagedType.I1, SizeConst=1)]public byte rgbAttr3;
}
}
----------------------------------------------------------------------------
excel确实很小，对了，我忘记把C#的test代码放上来了，生成的excel才554字节。
下面是根据灵感老大的demo我用C#翻的代码，请看：
private void button1_Click(object sender, System.EventArgs e)
{
SmartExcel excel = new SmartExcel();
excel.CreateFile("c:\\test.xls");
excel.PrintGridLines = false;
double height = 1.5;
excel.SetMargin(MarginTypes.TopMargin, height);
excel.SetMargin(MarginTypes.BottomMargin, height);
excel.SetMargin(MarginTypes.LeftMargin, height);
excel.SetMargin(MarginTypes.RightMargin, height);
string font = "Arial";
short fontsize = 12;
excel.SetFont(font,fontsize,FontFormatting.Italic);
byte b1 = 1,b2 = 12;
short s3 = 18;
excel.SetColumnWidth(b1,b2,s3);
string header = "头";
string footer = "角";
excel.SetHeader(header);
excel .SetFooter(footer);
int row = 1,col = 1,cellformat = 0;
object title = "没有使用任何EXCEL组件，直接写成了一个EXCEL文件，cool吧？！";
excel.WriteValue(ValueTypes.Text,CellFont.Font0,CellAlignment.LeftAlign,CellHiddenLocked.Normal,row,col,title,cellformat);
excel.CloseFile();
}
对了，代码中原来的ref参数的处理，都被我去掉了。