Esempio n. 1
1
    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.
    }
Esempio n. 2
0
        public void GetBytesTwice()
        {
            XlsDocument doc            = new XlsDocument();
            int         expectedLength = doc.Bytes.ByteArray.Length;

            Assert.AreEqual(expectedLength, doc.Bytes.ByteArray.Length);
        }
Esempio n. 3
0
        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");
        }
Esempio n. 4
0
            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);
            }
Esempio n. 5
0
        /// <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();
        }
Esempio n. 6
0
        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);
        }
Esempio n. 7
0
        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");
        }
Esempio n. 8
0
        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();
        }
Esempio n. 9
0
        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");
        }
Esempio n. 10
0
 // 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;
 }
Esempio n. 11
0
 // 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();
 }
Esempio n. 12
0
 // 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;
 }
Esempio n. 13
0
        /// <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();
        }
Esempio n. 14
0
        // 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));
        }
Esempio n. 15
0
        // 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));
        }
Esempio n. 16
0
 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();
 }
Esempio n. 17
0
        /// <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();
        }
Esempio n. 18
0
File: XF.cs Progetto: radtek/shi5588
//        private const ushort DEFAULT_LINE_COLOUR_INDEX = 8;

        internal XF(XlsDocument doc)
        {
            _doc = doc;

            _id = null;

            SetDefaults();
        }
Esempio n. 19
0
        //        private const ushort DEFAULT_LINE_COLOUR_INDEX = 8;
        internal XF(XlsDocument doc)
        {
            _doc = doc;

            _id = null;

            SetDefaults();
        }
Esempio n. 20
0
        private static Worksheet GetBaseMergeAreaOverlapTestSheet()
        {
            XlsDocument doc   = new XlsDocument();
            Worksheet   sheet = doc.Workbook.Worksheets.Add("Sheet1");

            sheet.Cells.Merge(2, 4, 10, 15);
            return(sheet);
        }
Esempio n. 21
0
        internal Style(XlsDocument doc, XF xf)
        {
            _isInitializing = true;

            _doc = doc;
            _xf  = xf;

            _isInitializing = false;
        }
Esempio n. 22
0
            //[Test]
            public void EmptyWorkbook()
            {
                XlsDocument doc      = new XlsDocument();
                string      expected = "..\\..\\..\\Docs\\empty.xls";
                string      actual   = "empty.xls";

                WriteBytesToFile(doc.Bytes, actual);
                AssertFilesBinaryEqual(expected, actual);
            }
Esempio n. 23
0
 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);
 }
Esempio n. 24
0
        internal Style(XlsDocument doc, XF xf)
        {
            _isInitializing = true;

            _doc = doc;
            _xf = xf;

            _isInitializing = false;
        }
Esempio n. 25
0
        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);
        }
Esempio n. 26
0
 // 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);
 }
Esempio n. 27
0
 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");
 }
Esempio n. 28
0
        internal XFs(XlsDocument doc, Workbook workbook)
        {
            _doc      = doc;
            _workbook = workbook;

            _xfs = new List <XF>();

            AddDefaultStyleXF();
            AddDefaultUserXF();
        }
Esempio n. 29
0
        // 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);
        }
Esempio n. 30
0
        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));
        }
Esempio n. 31
0
        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");
        }
Esempio n. 32
0
        internal XFs(XlsDocument doc, Workbook workbook)
        {
            _doc = doc;
            _workbook = workbook;

            _xfs = new List<XF>();

            AddDefaultStyleXFs();
            AddDefaultUserXF();
            //AddDefaultFormattedStyleXFs();  //what was I thinking about here?
        }
Esempio n. 33
0
        internal XFs(XlsDocument doc, Workbook workbook)
        {
            _doc      = doc;
            _workbook = workbook;

            _xfs = new List <XF>();

            AddDefaultStyleXFs();
            AddDefaultUserXF();
            //AddDefaultFormattedStyleXFs();  //what was I thinking about here?
        }
Esempio n. 34
0
        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);
        }
Esempio n. 35
0
        internal Font(XlsDocument doc)
        {
            _isInitializing = true;

            _doc = doc;
            _id  = null;

            SetDefaults();

            _isInitializing = false;
        }
Esempio n. 36
0
        internal Font(XlsDocument doc)
        {
            _isInitializing = true;

            _doc = doc;
            _id = null;

            SetDefaults();

            _isInitializing = false;
        }
Esempio n. 37
0
 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);
 }
Esempio n. 38
0
        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
                    }
                }
            }
        }
Esempio n. 39
0
        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);
        }
Esempio n. 40
0
        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);
            }
        }
Esempio n. 41
0
 /// <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();
 }
Esempio n. 42
0
        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);
        }
Esempio n. 43
0
 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");
 }
Esempio n. 44
0
 /// <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);
     }
 }
Esempio n. 45
0
    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();
    }
Esempio n. 46
0
        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();
        }
Esempio n. 47
0
        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>();
        }
Esempio n. 48
0
        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;
        }
Esempio n. 49
0
        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);
        }
Esempio n. 50
0
 internal Font(XlsDocument doc, Bytes bytes)
     : this(doc)
 {
     ReadBytes(bytes);
 }
Esempio n. 51
0
 internal Font(XlsDocument doc, XF xf)
     : this(doc)
 {
     _target = xf;
 }
Esempio n. 52
0
        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();
        }
Esempio n. 53
0
        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;
        }
Esempio n. 54
0
 public void Run()
 {
     XlsDocument xls = new XlsDocument(); //Create a new XlsDocument
     xls.Send(); //Send it to the client
 }
Esempio n. 55
0
        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();
        }
Esempio n. 56
0
 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();
 }
Esempio n. 57
0
        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();
        }
Esempio n. 58
0
        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);
        }
Esempio n. 59
0
 private int GetExcelVbaColorIndex(XlsDocument doc, Color color)
 {
     return doc.Workbook.Palette.GetIndex(color) - 7;
 }
Esempio n. 60
0
        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;
                }
            }
        }