Excel reading and creation method 3: myxls

Excel reading and creation method 3: myxls

Calling the Excel component in the background to generate an Excel file. Although the Excel file can be fully controlled and any complicated format can be generated, it has a major disadvantage, this method will generate many Excel processes and it is difficult to completely clear them. Especially when an error occurs, the entire server may crash. This article introduces an open-source component written in C # and briefly introduces the XML format supported by office2003 and later versions.

 1. Excel binary format

The Excel File Format (biff8) specification and Microsoft compounddocument (ole2) format specification documents released by OpenOffice.org provide a detailed description of the Excel binary format, we can directly operate on the office binary format document.

Myxls is an open-source component written in C #. It can be used to generate an Excel file with many tables in the format. It provides an object-based API that is very easy to use.

1. generate an empty table

3. Create a complex vertex table.

Protected void page_load (Object sender, eventargs E)
{
Org. in2bits. myxls. xlsdocument Doc = new xlsdocument ();
Doc. filename = "testingagain.xls ";

// Doc. Workbook. protectcontents = true;

For (int s = 1; S <= 5; s ++)
{
String sheetname = request. Form ["txtsheet" + S]. Replace (",", String. Empty );

If (sheetname. Trim () = string. Empty)
Continue;

Int rowmin, rowcount, colmin, colcount;

Try
{
Rowmin = int. parse (request. Form ["txtrowmin" + S]);
Rowcount = int. parse (request. Form ["txtrows" + S]);
Colmin = int. parse (request. Form ["txtcolmin" + S]);
Colcount = int. parse (request. Form ["txtcols" + S]);
}
Catch
{
Continue;
}

If (rowcount> 65535) rowcount = 65535;
If (rowcount <0) rowcount = 0;
If (rowmin <1) rowmin = 1;
If (rowmin> 32767) rowmin = 32767;

If (colcount> 255) colcount = 255;
If (colcount <1) colcount = 1;
If (colmin <1) colmin = 1;
If (colmin> 100) colmin = 100;

If (sheetname. length> 35) sheetname = sheetname. substring (0, 35 );

Worksheet sheet = Doc. Workbook. worksheets. Add (sheetname );
// Sheet. Protected = true;
Cells = sheet. cells;

For (int row = 0; row <= rowcount; row ++)
{
If (ROW = 0)
{
For (INT Col = 1; Col <= colcount; Col ++)
{
Cell cell = cells. Add (rowmin + row, colmin + col-1, "rows" + col );
Cell. Font. Weight = 700;
Cell. diagonalascending = true;
Cell. diagonallinecolor = colors. Black;
Cell. diagonallinestyle = 2;
// Cell. pattern = 18;
// Cell. patterncolorindex = 0;
// Cell. patternbackgroundcolor = colors. Green;
Cell. Locked = true;
}
}
Else
{
For (INT Col = 1; Col <= colcount; Col ++)
{
Cell cell = cells. Add (rowmin + row, colmin + col-1,/* row + Col */1.001 );
Cell. Locked = false;
}
}
}
}

Doc. Send ();
Response. Flush ();
Response. End ();
}

1 xlsdocument XLS = new xlsdocument (); // create an empty Excel document
2
3 XLS. Send (); // send the document to the browser.

2. Create a complex vertex table

Xlsdocument XLS = new xlsdocument ();
XLS. filename = "wacky.xls ";

// Add file attributes
XLS. summaryinformation. Author = "Tim Erickson"; // prepared
XLS. summaryinformation. Subject = "a wacky display of Excel file generation ";
XLS. documentsummaryinformation. Company = "in2bits.org ";

For (INT sheetnumber = 1; sheetnumber <= 5; sheetnumber ++)
{
String sheetname = "sheet" + sheetnumber;
Int rowmin = sheetnumber;
Int rowcount = sheetnumber + 10;
Int colmin = sheetnumber;
Int colcount = sheetnumber + 10;
// Create 5 tables
Worksheet sheet = XLS. Workbook. worksheets. addnamed (sheetname );
Cells = sheet. cells;
For (INT r = 0; r <rowcount; r ++)
{
If (r = 0)
{
For (int c = 0; C <colcount; C ++)
{
// Create colcount cells in a row
Cells. Add (rowmin + R, colmin + C, "rows" + (C + 1). Font. Bold = true;
}
}
Else
{
For (int c = 0; C <colcount; C ++)
{
Int val = R + C;
Cell cell = cells. Add (rowmin + R, colmin + C, Val );
If (Val % 2! = 0)
{
Cell. Font. fontname = "Times New Roman ";
Cell. Font. Underline = underlinetypes. Double; // Add a Double underline to the bottom of the text.
Cell. Rotation = 45; // rotate cell text 45 degrees
}
}
}
}
}