protected void Page_Load(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument(); //Create a new MyXls Document
xls.Send(); //MyXls adds a single empty sheet named Sheet1 when Send
//is called, if you didn't add one yourself.
}
public void GetBytesTwice()
{
XlsDocument doc = new XlsDocument();
int expectedLength = doc.Bytes.ByteArray.Length;
Assert.AreEqual(expectedLength, doc.Bytes.ByteArray.Length);
}
public void SharedStringToggle()
{
XlsDocument shareOff = new XlsDocument();
Assert.IsFalse(shareOff.Workbook.ShareStrings, "Default ShareStrings setting");
XlsDocument shareOn = new XlsDocument();
shareOn.Workbook.ShareStrings = true;
Worksheet sheetOff = shareOff.Workbook.Worksheets.Add("Sheet1");
Worksheet sheetOn = shareOn.Workbook.Worksheets.Add("Sheet1");
string text = "abc";
sheetOff.Cells.Add(1, 1, text);
sheetOff.Cells.Add(1, 2, text);
sheetOn.Cells.Add(1, 1, text);
sheetOn.Cells.Add(1, 2, text);
Assert.AreEqual(0, shareOff.Workbook.SharedStringTable.CountUnique,
"ShareStrings false should have 0 SharedStringTable entries");
Assert.AreEqual(1, shareOn.Workbook.SharedStringTable.CountUnique,
"ShareStrings true should have 1 SharedStringTable entry");
}
private XlsDocument GetSimple(string sbase, int sct, int rct, int cct, int rmin, int cmin)
{
XlsDocument doc = new XlsDocument();
for (int s = 1; s <= sct; s++)
{
string sName = string.Format("{0}{1}", sbase, s);
if (sName.Length > 35)
{
sName = sName.Substring(0, 35);
}
Worksheet sht = doc.Workbook.Worksheets.AddNamed(sName); //Add sheet (and name it)
Cells cells = sht.Cells; //Grab Sheet.Cells Collection object
for (int r = 0; r <= rct; r++)
{
if (r == 0) //Add Header row
{
for (int c = 1; c <= cct; c++) //Iterate columns
{
cells.AddValueCell((ushort)(rmin + r), (ushort)(cmin + c - 1), "Fld" + c); //Add Header cells
}
}
else //Write data row
{
for (int c = 1; c <= cct; c++) //Iterate columns
{
cells.AddValueCell((ushort)(rmin + r), (ushort)(cmin + c - 1), -1 * (r + c)); //Add Value cells
}
}
}
}
return(doc);
}
/// <summary>
/// Iniitalizes a new instance of the Font class for the given XlsDocument.
/// </summary>
/// <param name="doc">The parent XlsDocument object for the new Font object.</param>
public Font(XlsDocument doc)
{
_doc = doc;
_id = null;
SetDefaults();
}
public void SstContinueRecordNoSplitFile()
{
// This is basically the same as the test above except it uses a
// file created by Excel and has two CONTINUE records
// (632 + 632 + 1 = 1265).
string fileName = TestsConfig.ReferenceFileFolder + "SstContinue.xls";
List <Record> continueWorkbookRecords = null;
Workbook.BytesReadCallback bytesReadCallback = delegate(List <Record> records)
{
continueWorkbookRecords = records;
};
XlsDocument xls = new XlsDocument(fileName, bytesReadCallback);
Assert.IsNotNull(continueWorkbookRecords, "Workbook records list");
Record sst = GetSstRecord(continueWorkbookRecords);
Assert.IsNotNull(sst, "SST Record");
Assert.AreEqual(2, sst.Continues.Count, "SST Continues");
Assert.AreEqual(1, xls.Workbook.Worksheets.Count);
Worksheet ws = xls.Workbook.Worksheets[0];
Assert.AreEqual(1266, ws.Rows.Count);
Assert.AreEqual(1265, ws.Rows.MaxRow);
Assert.AreEqual("Test000632", ws.Rows[632].CellAtCol(1).Value);
Assert.AreEqual("Test001264", ws.Rows[1264].CellAtCol(1).Value);
Assert.AreEqual("Test001265", ws.Rows[1265].CellAtCol(1).Value);
}
public void Read2007BlankBudgetWorksheet()
{
string fileName = Path.Combine(TestsConfig.ReferenceFileFolder, "BlankBudgetWorksheet.xls");
List <Record> workbookRecords;
Workbook.BytesReadCallback workbookBytesReadCallback = delegate(List <Record> records)
{
workbookRecords = records;
};
XlsDocument xls = new XlsDocument(fileName, workbookBytesReadCallback);
Assert.AreEqual(3, xls.Workbook.Worksheets.Count, "Number of worksheets");
Assert.AreEqual("Budget", xls.Workbook.Worksheets[0].Name, "Worksheet 1 name");
Assert.AreEqual("Income", xls.Workbook.Worksheets[1].Name, "Worksheet 2 name");
Assert.AreEqual("Expenses", xls.Workbook.Worksheets[2].Name, "Worksheet 3 name");
Worksheet sheet = xls.Workbook.Worksheets[0];
Assert.AreEqual("See reverse for instructions and guidelines", sheet.Rows[6].CellAtCol(8).Value,
"Cell H6 hyperlink text");
Assert.AreEqual("Budget Plan", sheet.Rows[7].CellAtCol(10).Value, "Cell J7 value");
Assert.AreEqual("Administrative Support (12% of Revenue)", sheet.Rows[28].CellAtCol(7).Value,
"Cell G28 value");
Assert.AreEqual(6801, sheet.Rows[10].CellAtCol(3).Value, "Cell C10 value");
Assert.AreEqual("- 20", sheet.Rows[10].CellAtCol(6).Value, "Cell F10 Formula result value");
Assert.AreEqual(0, sheet.Rows[10].CellAtCol(10).Value, "Cell J10 Multi-RK Value");
Assert.AreEqual(0, sheet.Rows[10].CellAtCol(11).Value, "Cell J11 Multi-RK Value");
}
private void buttonReadFile_Click(object sender, EventArgs e)
{
string fileName = textBoxReadFile.Text;
if (!File.Exists(fileName))
{
MessageBox.Show(string.Format("{0} not found!", fileName));
return;
}
XlsDocument xls = new XlsDocument(fileName);
Bytes stream = xls.OLEDoc.Streams[xls.OLEDoc.Streams.GetIndex(org.in2bits.MyOle2.Directory.Biff8Workbook)].Bytes;
List <Record> records = Record.GetAll(stream);
StringBuilder sb = new StringBuilder();
foreach (Record record in records)
{
string name = RID.Name(record.RID);
sb.Append(name);
sb.Append(new string(' ', RID.NAME_MAX_LENGTH - name.Length) + ": ");
byte[] recordData = record.Data.ByteArray;
for (int i = 0; i < recordData.Length; i++)
{
if (i > 0)
{
sb.Append(" ");
}
sb.Append(string.Format("{0:x2}", recordData[i]));
}
sb.Append(Environment.NewLine);
}
richTextBoxRecordList.Text = sb.ToString();
}
public void SetPatternBackgroundColor()
{
//http://www.mvps.org/dmcritchie/excel/colors.htm
XlsDocument theDoc = null;
Color colorA1 = Colors.Red;
Color colorA2 = Colors.Green;
Color colorA3 = Colors.Blue;
XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
theDoc = doc;
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cell cell;
cell = sheet.Cells.Add(1, 1, "red");
cell.Pattern = FillPattern.Percent50;
cell.PatternBackgroundColor = colorA1;
cell = sheet.Cells.Add(1, 2, "green");
cell.Pattern = FillPattern.Percent50;
cell.PatternBackgroundColor = colorA2;
cell = sheet.Cells.Add(1, 3, "blue");
cell.Pattern = FillPattern.Percent50;
cell.PatternBackgroundColor = colorA3;
};
string fileName = WriteDocument(docDelegate);
AssertPropertyViaExcelOle(1, 1, 1, fileName, CellProperties.PatternBackgroundColor, GetExcelVbaColorIndex(theDoc, colorA1).ToString(),
"Cell A1 should be red");
AssertPropertyViaExcelOle(1, 1, 2, fileName, CellProperties.PatternBackgroundColor, GetExcelVbaColorIndex(theDoc, colorA2).ToString(),
"Cell A2 should be green");
AssertPropertyViaExcelOle(1, 1, 3, fileName, CellProperties.PatternBackgroundColor, GetExcelVbaColorIndex(theDoc, colorA3).ToString(),
"Cell A3 should be Blue");
}
// Token: 0x06000043 RID: 67 RVA: 0x000034E1 File Offset: 0x000024E1
internal Style(XlsDocument doc, XF xf)
{
this._isInitializing = true;
this._doc = doc;
this._xf = xf;
this._isInitializing = false;
}
// Token: 0x0600028B RID: 651 RVA: 0x0000C01D File Offset: 0x0000B01D
internal XFs(XlsDocument doc, Workbook workbook)
{
this._doc = doc;
this._workbook = workbook;
this._xfs = new List <XF>();
this.AddDefaultStyleXFs();
this.AddDefaultUserXF();
}
// Token: 0x060001F7 RID: 503 RVA: 0x00009FD4 File Offset: 0x00008FD4
internal Font(XlsDocument doc)
{
this._isInitializing = true;
this._doc = doc;
this._id = null;
this.SetDefaults();
this._isInitializing = false;
}
/// <summary>
/// Initializes a new instance of the Fonts collection for the given XlsDocument.
/// </summary>
/// <param name="doc">The parent XlsDocument object for the new Fonts collection.</param>
public Fonts(XlsDocument doc)
{
_doc = doc;
_fonts = new List <Font>();
AddDefaultFonts();
}
// Token: 0x06000147 RID: 327 RVA: 0x000073C0 File Offset: 0x000063C0
private Bytes LABEL()
{
Bytes bytes = new Bytes();
bytes.Append(this.LABELBase());
bytes.Append(XlsDocument.GetUnicodeString(((string)this.Value) ?? string.Empty, 16));
return(Record.GetBytes(RID.LABEL, bytes));
}
// Token: 0x0600029E RID: 670 RVA: 0x0000C7E4 File Offset: 0x0000B7E4
private Bytes GetFormatRecord(ushort id, string format)
{
Bytes bytes = new Bytes();
bytes.Append(BitConverter.GetBytes(id));
bytes.Append(XlsDocument.GetUnicodeString(format, 16));
return(Record.GetBytes(RID.FORMAT, bytes));
}
protected void Page_Load(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();
xls.FileName = "MyXLS Hello World.xls";
Worksheet sheet = xls.Workbook.Worksheets.AddNamed("Hello, World!");
sheet.Cells.AddValueCell(1, 1, "Hello, World!");
xls.Send();
}
/// <summary>
/// Initializes a new instance of the Fonts collection for the given XlsDocument.
/// </summary>
/// <param name="doc">The parent XlsDocument object for the new Fonts collection.</param>
public Fonts(XlsDocument doc)
{
_doc = doc;
_fonts = new List<Font>();
AddDefaultFonts();
}
// private const ushort DEFAULT_LINE_COLOUR_INDEX = 8;
internal XF(XlsDocument doc)
{
_doc = doc;
_id = null;
SetDefaults();
}
// private const ushort DEFAULT_LINE_COLOUR_INDEX = 8;
internal XF(XlsDocument doc)
{
_doc = doc;
_id = null;
SetDefaults();
}
private static Worksheet GetBaseMergeAreaOverlapTestSheet()
{
XlsDocument doc = new XlsDocument();
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
sheet.Cells.Merge(2, 4, 10, 15);
return(sheet);
}
internal Style(XlsDocument doc, XF xf)
{
_isInitializing = true;
_doc = doc;
_xf = xf;
_isInitializing = false;
}
//[Test]
public void EmptyWorkbook()
{
XlsDocument doc = new XlsDocument();
string expected = "..\\..\\..\\Docs\\empty.xls";
string actual = "empty.xls";
WriteBytesToFile(doc.Bytes, actual);
AssertFilesBinaryEqual(expected, actual);
}
public void EmptyWorkbook()
{
XlsDocument doc = new XlsDocument();
SetTestMetadata(doc);
string expected = TestsConfig.ReferenceFileFolder + "empty.xls";
string actual = "empty.xls";
WriteBytesToFile(doc.Bytes, actual);
AssertFilesBinaryEqual(expected, actual);
}
internal Style(XlsDocument doc, XF xf)
{
_isInitializing = true;
_doc = doc;
_xf = xf;
_isInitializing = false;
}
internal Workbook(XlsDocument doc)
{
_doc = doc;
_worksheets = new Worksheets(_doc);
_fonts = new Fonts(_doc);
_formats = new Formats(_doc);
_styles = new Styles(_doc);
_xfs = new XFs(_doc, this);
}
// Token: 0x060000BA RID: 186 RVA: 0x00005C48 File Offset: 0x00004C48
internal Workbook(XlsDocument doc)
{
this._doc = doc;
this._worksheets = new Worksheets(this._doc);
this._fonts = new Fonts(this._doc);
this._formats = new Formats(this._doc);
this._styles = new Styles(this._doc);
this._xfs = new XFs(this._doc, this);
this._palette = new Palette(this);
}
public void RenameWorksheet()
{
XlsDocument doc = new XlsDocument();
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Worksheet foundSheet = doc.Workbook.Worksheets["Sheet1"];
Assert.IsNotNull(foundSheet, "Should find 'Sheet1' before renaming");
sheet.Name = "MySheet";
foundSheet = doc.Workbook.Worksheets["MySheet"];
Assert.IsNotNull(foundSheet, "Should find 'MySheet' after renaming");
}
internal XFs(XlsDocument doc, Workbook workbook)
{
_doc = doc;
_workbook = workbook;
_xfs = new List <XF>();
AddDefaultStyleXF();
AddDefaultUserXF();
}
// Token: 0x06000086 RID: 134 RVA: 0x00004B3C File Offset: 0x00003B3C
internal static Bytes GetUnicodeString(string text, int lengthBits)
{
int num = (lengthBits == 8) ? 255 : 65535;
byte[] byteArray = new byte[0];
byte[] array = new byte[0];
int length = text.Length;
if (length > num)
{
text = text.Substring(0, num);
}
if (num == 255)
{
byteArray = new byte[]
{
(byte)text.Length
};
}
else if (num == 65535)
{
byteArray = BitConverter.GetBytes((ushort)text.Length);
}
byte[] byteArray2;
if (XlsDocument.IsCompressible(text))
{
byteArray2 = new byte[1];
char[] array2 = text.ToCharArray();
array = new byte[array2.Length];
for (int i = 0; i < array2.Length; i++)
{
array[i] = (byte)array2[i];
}
}
else
{
byteArray2 = new byte[]
{
1
};
}
Bytes bytes = new Bytes();
bytes.Append(byteArray);
bytes.Append(byteArray2);
if (array.Length > 0)
{
bytes.Append(array);
}
else
{
bytes.Append(Encoding.Unicode.GetBytes(text));
}
return(bytes);
}
private Bytes LABEL()
{
Bytes label = new Bytes();
label.Append(LABELBase());
//Unicode string, 16-bit string length
label.Append(XlsDocument.GetUnicodeString((string)Value ?? string.Empty, 16));
return(Record.GetBytes(RID.LABEL, label));
}
public void RenameWorksheet()
{
XlsDocument doc = new XlsDocument();
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Worksheet foundSheet = doc.Workbook.Worksheets["Sheet1"];
Assert.IsNotNull(foundSheet, "Should find 'Sheet1' before renaming");
sheet.Name = "MySheet";
foundSheet = doc.Workbook.Worksheets["MySheet"];
Assert.IsNotNull(foundSheet, "Should find 'MySheet' after renaming");
}
internal XFs(XlsDocument doc, Workbook workbook)
{
_doc = doc;
_workbook = workbook;
_xfs = new List<XF>();
AddDefaultStyleXFs();
AddDefaultUserXF();
//AddDefaultFormattedStyleXFs(); //what was I thinking about here?
}
internal XFs(XlsDocument doc, Workbook workbook)
{
_doc = doc;
_workbook = workbook;
_xfs = new List <XF>();
AddDefaultStyleXFs();
AddDefaultUserXF();
//AddDefaultFormattedStyleXFs(); //what was I thinking about here?
}
public void EmptyWorkbook()
{
XlsDocument doc = new XlsDocument();
SetTestMetadata(doc);
string expected = TestsConfig.ReferenceFileFolder + "empty.xls";
string actual = "empty.xls";
WriteBytesToFile(doc.Bytes, actual);
AssertFilesBinaryEqual(expected, actual);
}
internal Font(XlsDocument doc)
{
_isInitializing = true;
_doc = doc;
_id = null;
SetDefaults();
_isInitializing = false;
}
internal Font(XlsDocument doc)
{
_isInitializing = true;
_doc = doc;
_id = null;
SetDefaults();
_isInitializing = false;
}
internal Workbook(XlsDocument doc)
{
_doc = doc;
_doc.Workbook = this;
_worksheets = new Worksheets(_doc);
_fonts = new Fonts(_doc);
_formats = new Formats(_doc);
_styles = new Styles(_doc);
_xfs = new XFs(_doc, this);
_palette = new Palette(this);
}
private void AddStrings(List <string> stringList, ref int remainingRecordBytes, ref Bytes bytes, Bytes sst, ref bool isFirstContinue)
{
foreach (string sharedString in stringList)
{
Bytes stringBytes = XlsDocument.GetUnicodeString(sharedString, 16);
//per excelfileformat.pdf sec. 5.22, can't split a
//Unicode string to another CONTINUE record before
//the first character's byte/s are written, and must
//repeat string option flags byte if it is split
//OPTIM: For smaller filesize, handle the possibility of compressing continued portion of uncompressed strings (low ROI!)
byte stringOptionFlag = 0xFF;
bool charsAre16Bit = false;
int minimumToAdd = int.MaxValue;
if (stringBytes.Length > remainingRecordBytes)
{
stringOptionFlag = stringBytes.Get(2, 1).ByteArray[0];
charsAre16Bit = (stringOptionFlag & 0x01) == 0x01;
minimumToAdd = charsAre16Bit ? 5 : 4;
}
while (stringBytes != null)
{
if (stringBytes.Length > remainingRecordBytes) //add what we can and continue
{
bool stringWasSplit = false;
if (remainingRecordBytes > minimumToAdd)
{
int overLength = (stringBytes.Length - remainingRecordBytes);
bytes.Append(stringBytes.Get(0, remainingRecordBytes));
stringBytes = stringBytes.Get(remainingRecordBytes, overLength);
remainingRecordBytes -= remainingRecordBytes;
stringWasSplit = true;
}
bytes = Continue(sst, bytes, out remainingRecordBytes, ref isFirstContinue);
if (stringWasSplit)
{
bytes.Append(stringOptionFlag);
remainingRecordBytes--;
}
}
else //add what's left
{
bytes.Append(stringBytes);
remainingRecordBytes -= stringBytes.Length;
stringBytes = null; //exit loop to continue to next sharedString
}
}
}
}
public void Rows2Cols1Row1Col1()
{
string expected = TestsConfig.ReferenceFileFolder + "Rows2Cols1Row1Col1.xls";
string actual = "Rows2Cols1Row1Col1.xls";
XlsDocument doc = GetSimple("BIFF8Sheet_", 1, 2, 1, 1, 1);
doc.FileName = actual;
WriteBytesToFile(doc.Bytes, actual);
AssertFilesBinaryEqual(expected, actual);
}
public void WriteNorthwindDataSetToXls()
{
string northwindXls = "Northwind.xls";
using (DataSet dataSet = GetNorthwindDataSet())
{
XlsDocument xls = new XlsDocument(dataSet);
SetTestMetadata(xls); //so metadata doesn't differ on different test machines and kill the compare
Assert.AreEqual(NORTHWIND_TABLE_NAMES.Length, xls.Workbook.Worksheets.Count, "Worksheet count");
xls.Save(true);
AssertFilesBinaryEqual(Path.Combine(TestsConfig.ReferenceFileFolder, northwindXls), northwindXls);
}
}
/// <summary>
/// export excel file
/// </summary>
/// <param name="excelInfo">DataTable need to be export.</param>
public void exportExcel(DataTable excelInfo)
{
var xls = new XlsDocument();
// set excel file
xls.FileName = HttpContext.Current.Server.UrlEncode(excelInfo.TableName) + ".xls"; //;"Detail.xls";
// create excel sheet page
Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");
// set column width
var ci = new ColumnInfo(xls, sheet);
ci.ColumnIndexStart = 1;
ci.ColumnIndexEnd = (ushort) (excelInfo.Columns.Count - 1);
ci.Width = 13*256;
sheet.AddColumnInfo(ci);
// output content
Cells cells = sheet.Cells;
// set title style
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
// output title
cells.Merge(1, 1, 1, excelInfo.Columns.Count);
cells.Add(1, 1, excelInfo.TableName, xf);
// append cell style
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.BottomLineStyle = 1;
xf.LeftLineStyle = 1;
xf.RightLineStyle = 1;
// output data
for (int i = 0; i < excelInfo.Columns.Count; i++)
{
// output output table header
cells.Add(2, i + 1, excelInfo.Columns[i].ColumnName, xf);
// output data
for (int j = 0; j < excelInfo.Rows.Count; j++)
{
// check data type
if (IsFloat(excelInfo.Rows[j][i].ToString()))
{
cells.Add(j + 3, i + 1, Convert.ToDecimal(excelInfo.Rows[j][i]), xf);
}
else
{
cells.Add(j + 3, i + 1, excelInfo.Rows[j][i].ToString(), xf);
}
}
}
xls.Send();
}
public static void DataReaderToExcelFile(IDataReader reader, string saveFileName, List<SheetOptions> optionsList)
{
if (reader == null) throw new ArgumentNullException("reader");
if (string.IsNullOrWhiteSpace(saveFileName)) throw new ArgumentException("字符串参数不允许为null或者空值.", saveFileName);
// 创建一个空Xls文档
XlsDocument xls = new XlsDocument();
int sheetIndex = 0;
int sheetDefaultNameIndex = 1;
do
{
// 创建一个工作表
SheetOptions options = (optionsList == null || sheetIndex >= optionsList.Count)
? options = new SheetOptions() { SheetName = SheetOptions.DefaultSheetName + (sheetDefaultNameIndex++).ToString() }
: optionsList[sheetIndex];
Worksheet sheet = xls.Workbook.Worksheets.Add(options.SheetName);
// 创建表格数据
if (reader.Read())
{
// 标题
ushort rowNo = 1,
columnNo = 1;
int fieldCnt = reader.FieldCount;
for (int i = 0; i < fieldCnt; i++, columnNo++)
{
sheet.Cells.Add(rowNo, columnNo, reader.GetName(i));
}
// 正文数据
do
{
rowNo++;
columnNo = 1;
for (int i = 0; i < fieldCnt; i++, columnNo++)
{
string value = Convert.ToString(reader[i]); // null值将显示为空
sheet.Cells.Add(rowNo, columnNo, value);
}
} while (reader.Read());
}
sheetIndex++;
} while (reader.NextResult());
trySaveXlsFile(xls, saveFileName);
}
public void ApplyCustomFormat()
{
XlsDocument doc = new XlsDocument();
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
Cell cell = sheet.Cells.Add(1, 1, 1.13);
Assert.AreEqual(1, doc.Workbook.Formats.Count, "Format count before applying new format");
cell.Format = "\"x\"#,##0.00_);(\"x\"#,##0.00)";
Assert.AreEqual(2, doc.Workbook.Formats.Count, "Format count after applying new format");
doc.FileName = "ApplyCustomFormat";
doc.Save(true);
string file = Environment.CurrentDirectory;
if (!file.EndsWith("\\"))
file += "\\";
file += doc.FileName;
AssertPropertyViaExcelOle(file, CellProperties.Text, "x1.13 ", "Cell Text");
}
/// <summary>
/// 构造函数
/// 初始化_XlsDoc对象
/// </summary>
public ExportExcel(MyDocument doc,string[] needColumns)
{
MyDoc = doc;
XlsDoc = new XlsDocument();
string sheetName = string.Format("{0}", MyDoc.DocName);
sheetName = string.IsNullOrEmpty(sheetName) ? "sheet1" : sheetName;
CurSheet = XlsDoc.Workbook.Worksheets.Add(sheetName);
if (needColumns == null || needColumns.Length == 0)
{
Content = ContentFactory.CreateInstance(MyDoc.Content);
}
else
{
Content = ContentFactory.CreateInstance(MyDoc.Content, needColumns);
}
}
protected void Page_Load(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();
xls.FileName = "Wacky.xls";
//Add some metadata (visible from Excel under File -> Properties)
xls.SummaryInformation.Author = "Tim Erickson"; //let them know who's responsible!
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;
Worksheet sheet = xls.Workbook.Worksheets.AddNamed(sheetName);
Cells cells = sheet.Cells;
for (int r = 0; r < rowCount; r++)
{
if (r == 0)
{
for (int c = 0; c < colCount; c++)
{
cells.Add(rowMin + r, colMin + c, "Fld" + (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;
cell.Rotation = 45;
}
}
}
}
}
xls.Send();
}
static void Main(string[] args)
{
// Download Image File OK.
/* */
Console.WriteLine(Environment.CurrentDirectory);
Console.WriteLine(AppDomain.CurrentDomain.BaseDirectory);
Console.WriteLine(Directory.GetCurrentDirectory());
Console.WriteLine();
XlsDocument doc = new XlsDocument();
Worksheet s = doc.Workbook.Worksheets.Add("Review");
Row row = s.Rows.AddRow(1);
Cell c = s.Cells.Add(1, 1, "id");
s.Cells.Add(5, 5, "content");
doc.Save(true);
//Console.ReadKey();
}
internal Worksheet(XlsDocument doc)
{
_doc = doc;
_visibility = WorksheetVisibilities.Default;
_sheettype = WorksheetTypes.Default;
_streamByteLength = 0;
_dbCellOffsets = new int[0];
_cells = new Cells(this);
_rows = new Rows();
_rowBlocks = new RowBlocks(this);
_cachedBlockRow = CachedBlockRow.Empty;
_columnInfos = new List<ColumnInfo>();
}
private void buttonGoMyXls_Click(object sender, EventArgs e)
{
int rows = 0;
if (!int.TryParse(textBoxRows.Text, out rows))
{
MessageBox.Show("Rows must be an int value!");
return;
}
int cols = 0;
if (!int.TryParse(textBoxColumns.Text, out cols))
{
MessageBox.Show("Columns must be an int value!");
return;
}
if (cols < 1 || cols > 255)
{
MessageBox.Show("Columns must be between 1 and 255!");
return;
}
if (rows < 1 || rows > 65535)
{
MessageBox.Show("Rows must be between 1 and 65,535!");
return;
}
XlsDocument doc = new XlsDocument();
Workbook wbk = doc.Workbook;
Worksheet sht = wbk.Worksheets.Add("Test Sheet");
for (int row = 0; row < rows; row++)
{
for (int col = 0; col < cols; col++)
{
if (row == 0)
{
sht.Cells.Add(row + 1, col + 1, string.Format("Fld{0}", col + 1));
}
else
{
sht.Cells.Add(row + 1, col + 1, 1);
}
}
}
byte[] bytes = doc.Bytes.ByteArray;
}
public void CellFontAndColorAreWrittenAndRead()
{
string filename = WriteDocument(delegate(XlsDocument doc)
{
Worksheet sheet = doc.Workbook.Worksheets.Add("Font");
Cell yellowCell = sheet.Cells.Add(1, 1, "Yellow Background");
yellowCell.PatternColor = Colors.Yellow;
yellowCell.Pattern = FillPattern.Solid;
yellowCell.Font.Bold = true;
yellowCell.Font.Color = Colors.Blue;
});
XlsDocument read = new XlsDocument(filename);
Cell c = read.Workbook.Worksheets[0].Rows[1].CellAtCol(1);
//Assert.AreEqual(Colors.Yellow, c.PatternColor); still not working written, not read correctly
//Assert.AreEqual(FillPattern.Solid, c.Pattern);
Assert.IsTrue(c.Font.Bold);
Assert.AreEqual(Colors.Blue, c.Font.Color);
}
internal Font(XlsDocument doc, Bytes bytes)
: this(doc)
{
ReadBytes(bytes);
}
internal Font(XlsDocument doc, XF xf)
: this(doc)
{
_target = xf;
}
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);
Cells 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, "Fld" + col);
cell.TopLineStyle = 2;
cell.TopLineColor = Colors.Black;
cell.BottomLineStyle = 2;
cell.BottomLineColor = Colors.Black;
if (col == 1)
{
cell.LeftLineStyle = 2;
cell.LeftLineColor = Colors.Black;
}
cell.RightLineStyle = 2;
cell.RightLineColor = Colors.Black;
cell.Font.Weight = FontWeight.Bold;
cell.Pattern = FillPattern.Solid;
cell.PatternColor = Colors.Silver;
}
}
else
{
for (int col = 1; col <= colCount; col++)
{
Cell cell = cells.Add(rowMin + row, colMin + col - 1, /*row + col*/1.001);
}
}
}
}
doc.Send();
Response.Flush();
Response.End();
}
private ushort _width = 2560; //Set default to 10-character column width
#endregion Fields
#region Constructors
/// <summary>
/// Initializes a new instance of the ColumnInfo class for the given Doc
/// and Worksheet.
/// </summary>
/// <param name="doc">The parent MyXls.Doc object for the new ColumnInfo object.</param>
/// <param name="worksheet">The parent MyXls.Worksheet object for the new ColumnInfo object.</param>
public ColumnInfo(XlsDocument doc, Worksheet worksheet)
{
_doc = doc;
_worksheet = worksheet;
}
public void Run()
{
XlsDocument xls = new XlsDocument(); //Create a new XlsDocument
xls.Send(); //Send it to the client
}
protected void btnExport_Click(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();//新建一个xls文档
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string MainID = Request.QueryString["ID"].ToString();
cs.DBCommand dbc = new cs.DBCommand();
string sqlmain = "select * from ETravel where ID=" + MainID;
DataTable dtMain = dbc.GetData("eReimbursement", sqlmain);
if (dtMain.Rows.Count != 1)
{
ErrorHandle("Data Error.");
return;
}
string sqlTocity = "select (ROW_NUMBER() OVER (ORDER BY Tocity)-1) % 4 AS SubRow,(ROW_NUMBER() OVER (ORDER BY Tocity) - 1) / 4 AS Row,Tocity from (select distinct Tocity from ETraveleDetail where [No]='" + MainID + "') t1";
DataTable dtTocity = dbc.GetData("eReimbursement", sqlTocity);
int pagecount = 0;
for (int i = 0; i < dtTocity.Rows.Count; i++)
{
if (Convert.ToInt32(dtTocity.Rows[i]["Row"].ToString())>pagecount)
{
pagecount = Convert.ToInt32(dtTocity.Rows[i]["Row"].ToString());
}
}
for (int j = 0; j < pagecount + 1; j++)
{
Worksheet sheet;
sheet = xls.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMddHHmmss" + j.ToString()));
//首行空白行
XF titleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF.UseBorder = false; // 使用边框
titleXF.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
//第二行
XF titleXF1 = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF1.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
titleXF1.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF1.UseBorder = false; // 使用边框
titleXF1.Font.Underline = UnderlineTypes.Single;
titleXF1.Font.Height = 18 * 20;
XF columnTitleXF41 = xls.NewXF();
XF columnTitleXF42 = xls.NewXF();
columnTitleXF42.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF42.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF42.Font.Height = 12 * 20;
XF columnTitleXF43 = xls.NewXF();
columnTitleXF43.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF43.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF43.Font.Height = 12 * 20;
columnTitleXF43.UseBorder = true;
columnTitleXF43.LeftLineStyle = 2;
columnTitleXF43.TopLineStyle = 2;
columnTitleXF43.RightLineStyle = 2;
columnTitleXF43.BottomLineStyle = 2;
XF columnTitleXF44 = xls.NewXF();
columnTitleXF44.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF44.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF44.Font.Height = 12 * 20;
columnTitleXF44.UseBorder = true;
columnTitleXF44.TopLineStyle = 2;
columnTitleXF44.BottomLineStyle = 2;
XF columnTitleXF46 = xls.NewXF();
columnTitleXF46.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF46.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF46.Font.Height = 12 * 20;
columnTitleXF46.UseBorder = true;
columnTitleXF46.TopLineStyle = 2;
columnTitleXF46.BottomLineStyle = 2;
columnTitleXF46.RightLineStyle = 2;
XF columnTitleXF412 = xls.NewXF();
columnTitleXF412.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF412.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF412.Font.Height = 12 * 20;
columnTitleXF412.UseBorder = true;
columnTitleXF412.TopLineStyle = 2;
columnTitleXF412.BottomLineStyle = 2;
columnTitleXF412.RightLineStyle = 2;
XF columnTitleXF62 = xls.NewXF();
columnTitleXF62.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF62.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF62.Font.Height = 10 * 20;
columnTitleXF62.Font.Bold = true;
columnTitleXF62.UseBorder = true;
columnTitleXF62.LeftLineStyle = 2;
columnTitleXF62.TopLineStyle = 2;
columnTitleXF62.RightLineStyle = 2;
columnTitleXF62.BottomLineStyle = 2;
XF columnTitleXF63 = xls.NewXF();
columnTitleXF63.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF63.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF63.Font.Height = 10 * 20;
columnTitleXF63.UseBorder = true;
columnTitleXF63.TopLineStyle = 2;
columnTitleXF63.LeftLineStyle = 1;
XF columnTitleXF64 = xls.NewXF();
columnTitleXF64.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF64.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF64.Font.Height = 10 * 20;
columnTitleXF64.UseBorder = true;
columnTitleXF64.TopLineStyle = 2;
columnTitleXF64.RightLineStyle = 1;
XF columnTitleXF66 = xls.NewXF();
columnTitleXF66.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF66.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF66.Font.Height = 10 * 20;
columnTitleXF66.UseBorder = true;
columnTitleXF66.TopLineStyle = 2;
XF columnTitleXF67 = xls.NewXF();
columnTitleXF67.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF67.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF67.Font.Height = 10 * 20;
columnTitleXF67.Font.Bold = true;
columnTitleXF67.UseBorder = true;
columnTitleXF67.LeftLineStyle = 2;
columnTitleXF67.TopLineStyle = 2;
columnTitleXF67.BottomLineStyle = 1;
XF columnTitleXF68 = xls.NewXF();
columnTitleXF68.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF68.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF68.Font.Height = 10 * 20;
columnTitleXF68.UseBorder = true;
columnTitleXF68.TopLineStyle = 2;
columnTitleXF68.RightLineStyle = 2;
XF columnTitleXF72 = xls.NewXF();
columnTitleXF72.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF72.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF72.Font.Height = 10 * 20;
columnTitleXF72.Font.Bold = true;
columnTitleXF72.UseBorder = true;
columnTitleXF72.LeftLineStyle = 2;
columnTitleXF72.RightLineStyle = 2;
columnTitleXF72.BottomLineStyle = 2;
XF columnTitleXF73 = xls.NewXF();
columnTitleXF73.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF73.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF73.Font.Height = 10 * 20;
columnTitleXF73.UseBorder = true;
columnTitleXF73.LeftLineStyle = 1;
columnTitleXF73.TopLineStyle = 1;
columnTitleXF73.RightLineStyle = 1;
columnTitleXF73.BottomLineStyle = 2;
XF columnTitleXF77 = xls.NewXF();
columnTitleXF77.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF77.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF77.Font.Height = 10 * 20;
columnTitleXF77.Font.Bold = true;
columnTitleXF77.UseBorder = true;
columnTitleXF77.LeftLineStyle = 2;
columnTitleXF77.TopLineStyle = 1;
columnTitleXF77.RightLineStyle = 1;
columnTitleXF77.BottomLineStyle = 2;
XF columnTitleXF78 = xls.NewXF();
columnTitleXF78.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF78.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF78.Font.Height = 10 * 20;
columnTitleXF78.Font.Bold = true;
columnTitleXF78.UseBorder = true;
columnTitleXF78.TopLineStyle = 1;
columnTitleXF78.RightLineStyle = 2;
columnTitleXF78.BottomLineStyle = 2;
XF columnTitleXF82 = xls.NewXF();
columnTitleXF82.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF82.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF82.Font.Height = 10 * 20;
columnTitleXF82.UseBorder = true;
columnTitleXF82.LeftLineStyle = 2;
columnTitleXF82.TopLineStyle = 2;
columnTitleXF82.RightLineStyle = 2;
columnTitleXF82.BottomLineStyle = 1;
XF columnTitleXF83 = xls.NewXF();
columnTitleXF83.HorizontalAlignment = HorizontalAlignments.Right;
columnTitleXF83.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF83.Font.Height = 10 * 20;
columnTitleXF83.UseBorder = true;
columnTitleXF83.LeftLineStyle = 1;
columnTitleXF83.TopLineStyle = 1;
columnTitleXF83.RightLineStyle = 1;
columnTitleXF83.BottomLineStyle = 1;
XF columnTitleXF812 = xls.NewXF();
columnTitleXF812.HorizontalAlignment = HorizontalAlignments.Right;
columnTitleXF812.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF812.Font.Height = 10 * 20;
columnTitleXF812.UseBorder = true;
columnTitleXF812.LeftLineStyle = 1;
columnTitleXF812.TopLineStyle = 1;
columnTitleXF812.RightLineStyle = 2;
columnTitleXF812.BottomLineStyle = 1;
XF columnTitleXF92 = xls.NewXF();
columnTitleXF92.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF92.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF92.Font.Height = 10 * 20;
columnTitleXF92.UseBorder = true;
columnTitleXF92.LeftLineStyle = 2;
columnTitleXF92.TopLineStyle = 1;
columnTitleXF92.RightLineStyle = 2;
columnTitleXF92.BottomLineStyle = 1;
XF columnTitleXF93 = xls.NewXF();
columnTitleXF93.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF93.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF93.Font.Height = 10 * 20;
columnTitleXF93.UseBorder = true;
columnTitleXF93.LeftLineStyle = 1;
columnTitleXF93.TopLineStyle = 1;
XF columnTitleXF192 = xls.NewXF();
columnTitleXF192.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF192.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF192.Font.Height = 10 * 20;
columnTitleXF192.UseBorder = true;
columnTitleXF192.LeftLineStyle = 2;
columnTitleXF192.TopLineStyle = 1;
columnTitleXF192.RightLineStyle = 2;
columnTitleXF192.BottomLineStyle = 2;
XF columnTitleXF202 = xls.NewXF();
columnTitleXF202.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF202.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF202.Font.Height = 10 * 20;
columnTitleXF202.UseBorder = true;
columnTitleXF202.LeftLineStyle = 2;
columnTitleXF202.TopLineStyle = 2;
XF columnTitleXF208 = xls.NewXF();
columnTitleXF208.HorizontalAlignment = HorizontalAlignments.Centered;
columnTitleXF208.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF208.Font.Height = 10 * 20;
columnTitleXF208.UseBorder = true;
columnTitleXF208.LeftLineStyle = 1;
XF columnTitleXF215 = xls.NewXF();
columnTitleXF215.HorizontalAlignment = HorizontalAlignments.Left;
columnTitleXF215.VerticalAlignment = VerticalAlignments.Centered;
columnTitleXF215.Font.Height = 10 * 20;
columnTitleXF215.UseBorder = true;
columnTitleXF215.LeftLineStyle = 1;
// 列标题行
ColumnInfo col1 = new ColumnInfo(xls, sheet); // 列对象
col1.ColumnIndexStart = 0; // 起始列为第1列，索引从0开始
col1.ColumnIndexEnd = 0; // 终止列为第1列，索引从0开始
col1.Width = 256; // 列的宽度计量单位为 1/256 字符宽
sheet.AddColumnInfo(col1); // 把格式附加到sheet页上
ColumnInfo col2 = new ColumnInfo(xls, sheet);
col2 = new ColumnInfo(xls, sheet); // 列对象
col2.ColumnIndexStart = 1;
col2.ColumnIndexEnd = 1;
col2.Width = 7680;
sheet.AddColumnInfo(col2);
ColumnInfo col3 = new ColumnInfo(xls, sheet);
col3 = new ColumnInfo(xls, sheet); // 列对象
col3.ColumnIndexStart = 2;
col3.ColumnIndexEnd = 9;
col3.Width = 1800;
sheet.AddColumnInfo(col3);
ColumnInfo col4 = new ColumnInfo(xls, sheet);
col4 = new ColumnInfo(xls, sheet); // 列对象
col4.ColumnIndexStart = 10;
col4.ColumnIndexEnd = 11;
col4.Width = 3900;
sheet.AddColumnInfo(col4);
//行
RowInfo rol1 = new RowInfo();
rol1.RowHeight = 10 * 20;
rol1.RowIndexStart = 1;
rol1.RowIndexEnd = 1;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 20 * 20;
rol1.RowIndexStart = 2;
rol1.RowIndexEnd = 2;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 5 * 20;
rol1.RowIndexStart = 3;
rol1.RowIndexEnd = 3;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 5 * 20;
rol1.RowIndexStart = 5;
rol1.RowIndexEnd = 5;
sheet.AddRowInfo(rol1);
rol1 = new RowInfo();
rol1.RowHeight = 18 * 20;
rol1.RowIndexStart = 6;
rol1.RowIndexEnd = 23;
sheet.AddRowInfo(rol1);
// 数据单元格样式
XF dataXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
dataXF.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
dataXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
dataXF.UseBorder = true; // 使用边框
dataXF.LeftLineStyle = 1; // 左边框样式
dataXF.LeftLineColor = Colors.Black; // 左边框颜色
dataXF.BottomLineStyle = 1; // 下边框样式
dataXF.BottomLineColor = Colors.Black; // 下边框颜色
dataXF.Font.FontName = "宋体";
dataXF.Font.Height = 9 * 20; // 设定字大小（字体大小是以 1/20 point 为单位的）
dataXF.UseProtection = false; // 默认的就是受保护的，导出后需要启用编辑才可修改
dataXF.TextWrapRight = true; // 自动换行
// 合并单元格
MergeArea titleArea = new MergeArea(1, 1, 1, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(2, 2, 1, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(3, 3, 1, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(4, 4, 3, 6);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(4, 4, 7, 10);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(4, 4, 11, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 11, 12);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 3, 4);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 5, 6);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 7, 8);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 6, 9, 10);
sheet.AddMergeArea(titleArea);
titleArea = new MergeArea(6, 7, 2, 2);
sheet.AddMergeArea(titleArea);
// 开始填充数据到单元格
org.in2bits.MyXls.Cells cells = sheet.Cells;
cells.Add(1, 1, "", titleXF);
cells.Add(2, 1, "Travel Expense Report", titleXF1);
cells.Add(3, 1, "", titleXF);
cells.Add(4, 1, "", columnTitleXF41);
cells.Add(4, 2, "Applicant:", columnTitleXF42);
cells.Add(4, 3, dtMain.Rows[0]["Person"].ToString(), columnTitleXF43);
cells.Add(4, 4, "", columnTitleXF44);
cells.Add(4, 5, "", columnTitleXF44);
cells.Add(4, 6, "", columnTitleXF46);
cells.Add(4, 7, "Travel Period:", columnTitleXF42);
string bb = "";
if (dtMain.Rows[0]["Bdate"].ToString()!="")
{
bb += Convert.ToDateTime(dtMain.Rows[0]["Bdate"].ToString()).ToString("yyyy/MM/dd");
}
if (dtMain.Rows[0]["Edate"].ToString() != "")
{
bb += " - " + Convert.ToDateTime(dtMain.Rows[0]["Edate"].ToString()).ToString("yyyy/MM/dd");
}
cells.Add(4, 11, bb, columnTitleXF43);
cells.Add(4, 12, "", columnTitleXF412);
cells.Add(5, 1, "", columnTitleXF41);
cells.Add(6, 1, "", columnTitleXF41);
cells.Add(6, 2, "Travel Destination", columnTitleXF62);
cells.Add(6, 3, "", columnTitleXF63);
cells.Add(6, 4, "", columnTitleXF64);
cells.Add(6, 5, "", columnTitleXF63);
cells.Add(6, 6, "", columnTitleXF64);
cells.Add(6, 7, "", columnTitleXF63);
cells.Add(6, 8, "", columnTitleXF64);
cells.Add(6, 9, "", columnTitleXF63);
cells.Add(6, 10, "", columnTitleXF64);
cells.Add(6, 11, "Total Expenses", columnTitleXF67);
cells.Add(6, 12, "", columnTitleXF68);
cells.Add(7, 1, "", columnTitleXF41);
cells.Add(7, 2, "", columnTitleXF72);
cells.Add(7, 3, "", columnTitleXF73);
cells.Add(7, 4, "", columnTitleXF73);
cells.Add(7, 5, "", columnTitleXF73);
cells.Add(7, 6, "", columnTitleXF73);
cells.Add(7, 7, "", columnTitleXF73);
cells.Add(7, 8, "", columnTitleXF73);
cells.Add(7, 9, "", columnTitleXF73);
cells.Add(7, 10, "", columnTitleXF73);
cells.Add(7, 11, "Reimbursement", columnTitleXF77);
cells.Add(7, 12, "Company Paid", columnTitleXF78);
cells.Add(8, 1, "", columnTitleXF41);
cells.Add(8, 2, "1. Air Ticket - Int'l", columnTitleXF82);
cells.Add(9, 1, "", columnTitleXF41);
cells.Add(9, 2, "Domestic", columnTitleXF92);
cells.Add(10, 1, "", columnTitleXF41);
cells.Add(10, 2, "2. Hotel Bill", columnTitleXF92);
cells.Add(11, 1, "", columnTitleXF41);
cells.Add(11, 2, "3. Meals", columnTitleXF92);
cells.Add(12, 1, "", columnTitleXF41);
cells.Add(12, 2, "4. Entertainment", columnTitleXF92);
cells.Add(13, 1, "", columnTitleXF41);
cells.Add(13, 2, "5. Car Rental/Transportation", columnTitleXF92);
cells.Add(14, 1, "", columnTitleXF41);
cells.Add(14, 2, "6. Communication", columnTitleXF92);
cells.Add(15, 1, "", columnTitleXF41);
cells.Add(15, 2, "7. Local Trip NTD800(CNY60)/day", columnTitleXF92);
cells.Add(16, 1, "", columnTitleXF41);
cells.Add(16, 2, "8. Overseas Trip USD15/day", columnTitleXF92);
cells.Add(17, 1, "", columnTitleXF41);
cells.Add(17, 2, "9. Airport Tax/Travel Insurance", columnTitleXF92);
cells.Add(18, 1, "", columnTitleXF41);
cells.Add(18, 2, "10. Others", columnTitleXF92);
cells.Add(19, 1, "", columnTitleXF41);
cells.Add(19, 2, "Total", columnTitleXF192);
cells.Add(20, 1, "", columnTitleXF41);
cells.Add(20, 2, "Remarks: USD: NTD =", columnTitleXF202);
cells.Add(20, 12, "Total Trip Expense", columnTitleXF208);
cells.Add(21, 7, "Less: Advance", columnTitleXF215);
cells.Add(22, 7, "Bal Due to Company", columnTitleXF215);
cells.Add(23, 7, "Bal. Due to Employee", columnTitleXF215);
for (int i = 0; i < 12; i++)
{
for (int ii = 0; ii < 9; ii++)
{
cells.Add(8 + i, 3 + ii, "", columnTitleXF83);
}
}
for (int i = 0; i < 12; i++)
{
cells.Add(8 + i, 12, "", columnTitleXF812);
}
string sqlDetail = "select * from ETraveleDetail where [No]='" + MainID + "'";
DataTable dtDetail = dbc.GetData("eReimbursement", sqlDetail);
decimal row1TC = 0M;
decimal row1TP = 0M;
decimal row2TC = 0M;
decimal row2TP = 0M;
decimal row3TC = 0M;
decimal row3TP = 0M;
decimal row4TC = 0M;
decimal row4TP = 0M;
decimal row5TC = 0M;
decimal row5TP = 0M;
decimal row6TC = 0M;
decimal row6TP = 0M;
decimal row7TC = 0M;
decimal row7TP = 0M;
decimal row8TC = 0M;
decimal row8TP = 0M;
decimal row9TC = 0M;
decimal row9TP = 0M;
decimal row10TC = 0M;
decimal row10TP = 0M;
decimal row11TC = 0M;
decimal row11TP = 0M;
for (int p = 0; p < dtTocity.Rows.Count; p++)
{
if (Convert.ToInt32(dtTocity.Rows[p]["Row"].ToString()) == j)
{
decimal row1Pamount = 0M;
decimal row1Camount = 0M;
decimal row2Pamount = 0M;
decimal row2Camount = 0M;
decimal row3Pamount = 0M;
decimal row3Camount = 0M;
decimal row4Pamount = 0M;
decimal row4Camount = 0M;
decimal row5Pamount = 0M;
decimal row5Camount = 0M;
decimal row6Pamount = 0M;
decimal row6Camount = 0M;
decimal row7Pamount = 0M;
decimal row7Camount = 0M;
decimal row8Pamount = 0M;
decimal row8Camount = 0M;
decimal row9Pamount = 0M;
decimal row9Camount = 0M;
decimal row10Pamount = 0M;
decimal row10Camount = 0M;
decimal row11Pamount = 0M;
decimal row11Camount = 0M;
decimal column0TC = 0M;
decimal column1TP = 0M;
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
if (dtDetail.Rows[i]["Tocity"].ToString() == dtTocity.Rows[p]["Tocity"].ToString())
{
if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012023")//Air Ticket - Int'l
{
row1Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row1Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row1TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row1TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012011" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012021")//Hotel Bill
{
row3Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row3Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row3TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row3TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62010901" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010910" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010920")//Enter
{
row5Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row5Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row5TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row5TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62011901" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011910" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011920" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011930" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011940" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012013")//Car
{
row6Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row6Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row6TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row6TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62010501" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010510" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010520" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010530" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010540" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010550" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010560")//Commu
{
row7Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row6Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row7TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row7TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012012")//Local
{
row8Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row8Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row8TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row8TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012022")//Oversea
{
row9Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row9Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row9TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row9TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62020630")//Airport
{
row10Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row10Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row10TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row10TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012014" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012024")//Others
{
row11Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row11TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
else
{
row11Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
row11TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
row11TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
column0TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
column1TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
}
}
cells.Add(6, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, dtTocity.Rows[p]["Tocity"].ToString(), columnTitleXF63);
cells.Add(7, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, "Reim", columnTitleXF73);
cells.Add(7, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, "Comp", columnTitleXF73);
if (row1Pamount != 0M)
{
cells.Add(8, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row1Pamount, columnTitleXF83);
}
if (row1Camount != 0M)
{
cells.Add(8, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row1Camount, columnTitleXF83);
}
if (row2Pamount != 0M)
{
cells.Add(9, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row2Pamount, columnTitleXF83);
}
if (row2Camount != 0M)
{
cells.Add(9, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row2Camount, columnTitleXF83);
}
if (row3Pamount != 0M)
{
cells.Add(10, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row3Pamount, columnTitleXF83);
}
if (row3Camount != 0M)
{
cells.Add(10, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row3Camount, columnTitleXF83);
}
if (row4Pamount != 0M)
{
cells.Add(11, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row4Pamount, columnTitleXF83);
}
if (row4Camount != 0M)
{
cells.Add(11, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row4Camount, columnTitleXF83);
}
if (row5Pamount != 0M)
{
cells.Add(12, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row5Pamount, columnTitleXF83);
}
if (row5Camount != 0M)
{
cells.Add(12, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row5Camount, columnTitleXF83);
}
if (row6Pamount != 0M)
{
cells.Add(13, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row6Pamount, columnTitleXF83);
}
if (row6Camount != 0M)
{
cells.Add(13, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row6Camount, columnTitleXF83);
}
if (row7Pamount != 0M)
{
cells.Add(14, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row7Pamount, columnTitleXF83);
}
if (row7Camount != 0M)
{
cells.Add(14, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row7Camount, columnTitleXF83);
}
if (row8Pamount != 0M)
{
cells.Add(15, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row8Pamount, columnTitleXF83);
}
if (row8Camount != 0M)
{
cells.Add(15, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row8Camount, columnTitleXF83);
}
if (row9Pamount != 0M)
{
cells.Add(16, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row9Pamount, columnTitleXF83);
}
if (row9Camount != 0M)
{
cells.Add(16, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row9Camount, columnTitleXF83);
}
if (row10Pamount != 0M)
{
cells.Add(17, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row10Pamount, columnTitleXF83);
}
if (row10Camount != 0M)
{
cells.Add(17, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row10Camount, columnTitleXF83);
}
if (row11Pamount != 0M)
{
cells.Add(18, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row11Pamount, columnTitleXF83);
}
if (row11Camount != 0M)
{
cells.Add(18, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row11Camount, columnTitleXF83);
}
if (column0TC != 0M)
{
cells.Add(19, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, column0TC, columnTitleXF83);
}
if (column1TP != 0M)
{
cells.Add(19, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, column1TP, columnTitleXF83);
}
}
}
if (row1TC != 0M)
{
cells.Add(8, 11, row1TC, columnTitleXF83);
}
if (row1TP != 0M)
{
cells.Add(8, 12, row1TP, columnTitleXF812);
}
if (row2TC != 0M)
{
cells.Add(9, 11, row2TC, columnTitleXF83);
}
if (row2TP != 0M)
{
cells.Add(9, 12, row2TP, columnTitleXF812);
}
if (row3TC != 0M)
{
cells.Add(10, 11, row3TC, columnTitleXF83);
}
if (row3TP != 0M)
{
cells.Add(10, 12, row3TP, columnTitleXF812);
}
if (row4TC != 0M)
{
cells.Add(11, 11, row4TC, columnTitleXF83);
}
if (row4TP != 0M)
{
cells.Add(11, 12, row4TP, columnTitleXF812);
}
if (row5TC != 0M)
{
cells.Add(12, 11, row5TC, columnTitleXF83);
}
if (row5TP != 0M)
{
cells.Add(12, 12, row5TP, columnTitleXF812);
}
if (row6TC != 0M)
{
cells.Add(13, 11, row6TC, columnTitleXF83);
}
if (row6TP != 0M)
{
cells.Add(13, 12, row6TP, columnTitleXF812);
}
if (row7TC != 0M)
{
cells.Add(14, 11, row7TC, columnTitleXF83);
}
if (row7TP != 0M)
{
cells.Add(14, 12, row7TP, columnTitleXF812);
}
if (row8TC != 0M)
{
cells.Add(15, 11, row8TC, columnTitleXF83);
}
if (row8TP != 0M)
{
cells.Add(15, 12, row8TP, columnTitleXF812);
}
if (row9TC != 0M)
{
cells.Add(16, 11, row9TC, columnTitleXF83);
}
if (row9TP != 0M)
{
cells.Add(16, 12, row9TP, columnTitleXF812);
}
if (row10TC != 0M)
{
cells.Add(17, 11, row10TC, columnTitleXF83);
}
if (row10TP != 0M)
{
cells.Add(17, 12, row10TP, columnTitleXF812);
}
if (row11TC != 0M)
{
cells.Add(18, 11, row11TC, columnTitleXF83);
}
if (row11TP != 0M)
{
cells.Add(18, 12, row11TP, columnTitleXF812);
}
decimal tc = row1TC + row2TC + row3TC + row4TC + row5TC + row6TC + row7TC + row8TC + row9TC + row10TC + row11TC;
if (tc!=0M)
{
cells.Add(19, 11, tc, columnTitleXF83);
}
decimal tp = row1TP + row2TP + row3TP + row4TP + row5TP + row6TP + row7TP + row8TP + row9TP + row11TP;
if (tp != 0M)
{
cells.Add(19, 12, tp, columnTitleXF812);
}
}
xls.Send();
}
private void buttonReadFile_Click(object sender, EventArgs e)
{
string fileName = textBoxReadFile.Text;
if (!File.Exists(fileName))
{
MessageBox.Show(string.Format("{0} not found!", fileName));
return;
}
XlsDocument xls = new XlsDocument(fileName);
Bytes stream = xls.OLEDoc.Streams[xls.OLEDoc.Streams.GetIndex(org.in2bits.MyOle2.Directory.Biff8Workbook)].Bytes;
List<Record> records = Record.GetAll(stream);
StringBuilder sb = new StringBuilder();
foreach (Record record in records)
{
string name = RID.Name(record.RID);
sb.Append(name);
sb.Append(new string(' ', RID.NAME_MAX_LENGTH - name.Length) + ": ");
byte[] recordData = record.Data.ByteArray;
for (int i = 0; i < recordData.Length; i++)
{
if (i > 0)
sb.Append(" ");
sb.Append(string.Format("{0:x2}", recordData[i]));
}
sb.Append(Environment.NewLine);
}
richTextBoxRecordList.Text = sb.ToString();
}
protected void btnExport_Click(object sender, EventArgs e)
{
XlsDocument xls = new XlsDocument();//新建一个xls文档
xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Worksheet sheet;
sheet = xls.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMddHHmmss"));
XF titleXF = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF.UseBorder = false; // 使用边框
titleXF.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
XF titleXF1 = xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
titleXF1.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
titleXF1.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
titleXF1.UseBorder = false; // 使用边框
titleXF1.Font.Bold = true;
titleXF1.Font.Height = 12 * 20; // 字大小（字体大小是以 1/20 point 为单位的）
// 开始填充数据到单元格
org.in2bits.MyXls.Cells cells = sheet.Cells;
cells.Add(1, 1, "NO#", titleXF1);
cells.Add(1, 2, "Claim Type", titleXF1);
cells.Add(1, 3, "Amount", titleXF1);
cells.Add(1, 4, "Owner", titleXF1);
cells.Add(1, 5, "Process", titleXF1);
cells.Add(1, 6, "Current Approver", titleXF1);
cells.Add(1, 7, "Submit Date", titleXF1);
cells.Add(1, 8, "Remark", titleXF1);
//添加数据
string json = GridData.Value.ToString();
StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
XmlNode xml = eSubmit.Xml;
XmlDocument doc = new XmlDocument();
doc.LoadXml(xml.InnerXml);
for (int i = 0; i < doc.SelectNodes("records").Item(0).SelectNodes("record").Count; i++)
{
if (!string.IsNullOrEmpty(doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Tamount").Item(0).InnerXml))
{
cells.Add(2 + i, 3, Convert.ToDouble(doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Tamount").Item(0).InnerXml), titleXF);
}
else
{
cells.Add(2 + i, 3, "", titleXF);
}
cells.Add(2 + i, 1, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("No").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 2, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Type1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 4, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Person").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 5, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Status1").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 6, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Approver").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 7, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("CreadedDate").Item(0).InnerXml, titleXF);
cells.Add(2 + i, 8, doc.SelectNodes("records").Item(0).SelectNodes("record").Item(i).SelectNodes("Remark").Item(0).InnerXml, titleXF);
}
xls.Send();
}
public static string WriteDocument(XlsDocumentDelegate docDelegate)
{
string path = Environment.CurrentDirectory;
if (!path.EndsWith("\\"))
path += "\\";
string fileName = "writedocument";
XlsDocument xls = new XlsDocument();
xls.FileName = fileName;
if (docDelegate != null)
docDelegate(xls);
xls.Save(path, true);
return string.Format("{0}{1}.xls", path, fileName);
}
private int GetExcelVbaColorIndex(XlsDocument doc, Color color)
{
return doc.Workbook.Palette.GetIndex(color) - 7;
}
public static void ToExcel(List<DataModel> dm,out string Msg)
{
Msg = "导入成功";
if(dm.Count<=0)
{
Msg="没有相应的数据";
return;
}
//行数不可以大于65536
if (dm.Count > 65536)
{
Msg="数据量太大，请减少时间区间进行查询";
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "Save Excel File";
sfd.Filter = "Excel File(*.xls)|*.xls";
//sfd.FilterIndex = 1;
sfd.OverwritePrompt = true;
sfd.DefaultExt = "xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
string FileName = sfd.FileName;
XlsDocument xls = new XlsDocument();//创建空xls文档
xls.FileName = FileName;
Worksheet sheet = xls.Workbook.Worksheets.AddNamed("Report"); //创建一个工作页为Report
////设定列宽度--Carton Number、Class、Module Number
//ColumnInfo colInfo0 = new ColumnInfo(xls, sheet);
//colInfo0.ColumnIndexStart = 0;
//colInfo0.ColumnIndexEnd = 0;
//colInfo0.Width = 20 * 256;
//sheet.AddColumnInfo(colInfo0);
//ColumnInfo colInfo1 = new ColumnInfo(xls, sheet);
//colInfo1.ColumnIndexStart = 1;
//colInfo1.ColumnIndexEnd = 1;
//colInfo1.Width = 15 * 256;
//sheet.AddColumnInfo(colInfo1);
//ColumnInfo colInfo2 = new ColumnInfo(xls, sheet);
//colInfo2.ColumnIndexStart = 2;
//colInfo2.ColumnIndexEnd = 2;
//colInfo2.Width = 18 * 256;
//sheet.AddColumnInfo(colInfo2);
//ColumnInfo colInfo3 = new ColumnInfo(xls, sheet);
//colInfo3.ColumnIndexStart = 3;
//colInfo3.ColumnIndexEnd = 3;
//colInfo3.Width = 10 * 256;
//sheet.AddColumnInfo(colInfo3);
//ColumnInfo colInfo8 = new ColumnInfo(xls, sheet);
//colInfo8.ColumnIndexStart = 8;
//colInfo8.ColumnIndexEnd = 8;
//colInfo8.Width = 12 * 256;
//sheet.AddColumnInfo(colInfo8);
//创建列
Cells cells = sheet.Cells; //获得指定工作页列集合
//创建表头
Cell title = cells.Add(1, 1, "车间");
title.HorizontalAlignment = HorizontalAlignments.Centered;
title.VerticalAlignment = VerticalAlignments.Centered;
Cell title1 = cells.Add(1, 2, "线别");
title1.HorizontalAlignment = HorizontalAlignments.Centered;
title1.VerticalAlignment = VerticalAlignments.Centered;
Cell title2 = cells.Add(1, 3, "设备名称");
title2.HorizontalAlignment = HorizontalAlignments.Centered;
title2.VerticalAlignment = VerticalAlignments.Centered;
Cell title3 = cells.Add(1, 4, "组件号");
title3.HorizontalAlignment = HorizontalAlignments.Centered;
title3.VerticalAlignment = VerticalAlignments.Centered;
Cell title4 = cells.Add(1, 5, "刷入时间");
title4.HorizontalAlignment = HorizontalAlignments.Centered;
title4.VerticalAlignment = VerticalAlignments.Centered;
int m=0;
double dtmp=0.00;
foreach(DataModel dataModel in dm)
{
m++;
Cell value = cells.Add(m + 1, 1, dataModel.WORKSHOP);
Cell value1 = cells.Add(m + 1, 2, dataModel.LINE);
Cell value2 = cells.Add(m + 1, 3, dataModel.EQUIPMENT_NAME);
Cell value3 = cells.Add(m + 1, 4, dataModel.MODULE_SN);
Cell value4 = cells.Add(m + 1, 5, dataModel.CREATED_ON);
value.HorizontalAlignment = HorizontalAlignments.Centered;
value.VerticalAlignment = VerticalAlignments.Centered;
value1.HorizontalAlignment = HorizontalAlignments.Centered;
value1.VerticalAlignment = VerticalAlignments.Centered;
value2.HorizontalAlignment = HorizontalAlignments.Centered;
value2.VerticalAlignment = VerticalAlignments.Centered;
value3.HorizontalAlignment = HorizontalAlignments.Centered;
value3.VerticalAlignment = VerticalAlignments.Centered;
value4.HorizontalAlignment = HorizontalAlignments.Centered;
value4.VerticalAlignment = VerticalAlignments.Centered;
}
try
{
xls.Save(true);
Msg = "导入成功";
}
catch(Exception e)
{
Msg="导入失败"+e.Message;
}
}
}