示例#1
1
        public static void ReadXlsSheetDataBySpreadSheet(GemBox.Spreadsheet.ExcelFile ExcelFileObj, DataTable SourceDataTable)
        {
            try
            {
                int RowCount = ExcelFileObj.Worksheets[0].Rows.Count;

                for (int i = 1; i < RowCount; i++)
                {
                    DataRow dr = SourceDataTable.NewRow();
                    bool newRow = false;
                    for (int j = 0; j < SourceDataTable.Columns.Count; j++)
                    {
                        dr[j] = ExcelFileObj.Worksheets[0].Rows[i].Cells[j].Value == null ? DBNull.Value : ExcelFileObj.Worksheets[0].Rows[i].Cells[j].Value;
                        if (dr[j] != DBNull.Value)
                            newRow = true;
                    }
                    if(newRow)
                        SourceDataTable.Rows.Add(dr);
                }
            }
            catch
            {
                throw;
            }
        }
示例#2
1
        /// <summary>
        /// 设置表格的title
        /// </summary>
        /// <param name="dcc"></param>
        /// <param name="worksheet"></param>
        void SetExcelTitle(DataColumnCollection dcc, GemBox.Spreadsheet.ExcelWorksheet worksheet)
        {
            int iColumnCount = dcc.Count;

            GemBox.Spreadsheet.CellRange range = worksheet.Cells.GetSubrangeAbsolute(0, 0, 0, iColumnCount - 1);//合并单元格
            range.Merged = true;
            worksheet.Rows[0].Cells[0].SetBorders(borderLeft, color, LineSyleThick);
            worksheet.Rows[0].Cells[0].SetBorders(borderRight, color, LineSyleThick);
            worksheet.Rows[0].Cells[0].SetBorders(borderTop, color, LineSyleThick);
            worksheet.Rows[0].Cells[0].SetBorders(borderBottom, color, LineSyleThick);

            string from = this.CADateTimeFrom.IsDateEmpty ? string.Empty : this.CADateTimeFrom.SelectedDate.ToString("yyyy-MM-dd");
            string to = this.CADateTimeTo.IsDateEmpty ? string.Empty : this.CADateTimeTo.SelectedDate.ToString("yyyy-MM-dd");

            worksheet.Rows[0].Cells[0].Value = string.Format("Nontrade List Report ({0} - {1})", from, to);
            for (int i = 0; i < iColumnCount; i++)
            {
                if (i == 0)
                {
                    worksheet.Rows[1].Cells[i].SetBorders(borderLeft, color, LineSyleThick);
                }
                else if (i == iColumnCount - 1)
                {
                    worksheet.Rows[1].Cells[i].SetBorders(borderLeft, color, LineSyleThin);
                    worksheet.Rows[1].Cells[i].SetBorders(borderRight, color, LineSyleThick);
                }
                else
                {
                    worksheet.Rows[1].Cells[i].SetBorders(borderLeft, color, LineSyleThin);
                }

                worksheet.Rows[1].Cells[i].SetBorders(borderTop, color, LineSyleThick);
                worksheet.Rows[1].Cells[i].SetBorders(borderBottom, color, LineSyleMedium);

                worksheet.Rows[1].Cells[i].Value = dcc[i].ColumnName;
            }
            //worksheet.Rows[1].Cells[iColumnCount - 1].SetBorders(borderRight, color, LineSyleThick);
        }
示例#3
0
        void SetContent(DataTable dt, GemBox.Spreadsheet.ExcelWorksheet worksheet)
        {
            int iCount = dt.Rows.Count;
            int iColumnCount = dt.Columns.Count;
            DataColumnCollection dcc = dt.Columns;
            for (int i = 0; i < iCount; i++)
            {
                for (int j = 0; j < iColumnCount; j++)
                {
                    int iRowNO = i + 2;
                    if (j == 0)//第一列
                    {
                        worksheet.Rows[iRowNO].Cells[j].SetBorders(borderLeft, color, LineSyleThick);
                    }
                    else if (j == (iColumnCount - 1))//最后一列
                    {
                        worksheet.Rows[iRowNO].Cells[j].SetBorders(borderRight, color, LineSyleThick);
                        worksheet.Rows[iRowNO].Cells[j].SetBorders(borderLeft, color, LineSyleThin);
                    }
                    else//中间列
                    {
                        worksheet.Rows[iRowNO].Cells[j].SetBorders(borderLeft, color, LineSyleThin);
                    }

                    if (i != (iCount - 1))
                    {
                        worksheet.Rows[iRowNO].Cells[j].SetBorders(borderBottom, color, LineSyleThin);
                    }
                    else //最后一行。
                    {
                        worksheet.Rows[iRowNO].Cells[j].SetBorders(borderBottom, color, LineSyleThick);
                    }

                    worksheet.Rows[iRowNO].Cells[j].Value = dt.Rows[i][j];
                }
            }
        }
示例#4
0
        /// <summary>
        /// 设置excel内的样式
        /// </summary>
        /// <param name="worksheet1"></param>
        /// <param name="iLastRowNO"></param>
        /// <param name="listColumnName"></param>
        void SetStyle(GemBox.Spreadsheet.ExcelWorksheet worksheet1, int iLastRowNO, List<string> listColumnName)
        {
            /*GemBox.Spreadsheet.MultipleBorders borderTop = GemBox.Spreadsheet.MultipleBorders.Top;
            GemBox.Spreadsheet.MultipleBorders borderLeft = GemBox.Spreadsheet.MultipleBorders.Left;
            GemBox.Spreadsheet.MultipleBorders borderRight = GemBox.Spreadsheet.MultipleBorders.Right;
            GemBox.Spreadsheet.MultipleBorders borderBottom = GemBox.Spreadsheet.MultipleBorders.Bottom;*/

            System.Drawing.Color color = System.Drawing.Color.Black;
            // GemBox.Spreadsheet.LineStyle LineSyleThin = GemBox.Spreadsheet.LineStyle.Thin;//细线
            GemBox.Spreadsheet.LineStyle LineSyleThick = GemBox.Spreadsheet.LineStyle.Thick;//粗实线

            GemBox.Spreadsheet.CellStyle cs14 = new GemBox.Spreadsheet.CellStyle();
            cs14.Font.Size = 200;
            cs14.HorizontalAlignment = GemBox.Spreadsheet.HorizontalAlignmentStyle.Center;

            /* GemBox.Spreadsheet.CellRange range = worksheet1.Cells.GetSubrangeAbsolute(0, 0, 0, 3);//合并单元格
             range.Merged = true;
             worksheet1.Rows[0].Cells[0].Value = "PRPOREPORT";
             worksheet1.Rows[0].Cells[0].SetBorders(borderTop, color, LineSyleThick);
             worksheet1.Rows[0].Cells[0].SetBorders(borderLeft, color, LineSyleThick);
             worksheet1.Rows[0].Cells[0].SetBorders(borderRight, color, LineSyleThick);
             worksheet1.Rows[0].Cells[0].Style.Font.Size=50;// = cs20;
             */
            int iCo = listColumnName.Count;
            for (int i = 0; i < iCo; i++)
            {

                worksheet1.Rows[0].Cells[i].Value = listColumnName[i];
                worksheet1.Rows[0].Cells[i].Style = cs14;
            }
        }