예제 #1
0
        /// <summary>
        /// 静态方法,读取规则的2维表的Excel成DataSet
        /// </summary>
        /// <param name="fileFullPath">全路径,包括文件名</param>
        /// <returns>DataSet</returns>
        public static DataSet Excel2DataSet(string fileFullPath)
        {
            DataSet   dsExcel = new DataSet();
            DataTable dt      = new DataTable();

            Infragistics.Documents.Excel.Workbook  workBook  = Infragistics.Documents.Excel.Workbook.Load(fileFullPath);
            Infragistics.Documents.Excel.Worksheet workSheet = workBook.Worksheets[0];

            int   maxColumnCount = Infragistics.Documents.Excel.Workbook.MaxExcelColumnCount;
            int   RowNum         = 0;
            Int32 columnCount    = 0;

            if (workSheet != null)
            {
                foreach (WorksheetRow row in workSheet.Rows)
                {
                    if (RowNum == 0)
                    {
                        Infragistics.Documents.Excel.WorksheetRow firstRow = row;

                        for (Int32 i = 0; i < maxColumnCount; i++)
                        {
                            if (firstRow.Cells[i].Value != null)
                            {
                                dt.Columns.Add(Convert.ToString(firstRow.Cells[i].Value));
                                columnCount = columnCount + 1;
                            }
                            else
                            {
                                break;
                            }
                        }
                    }
                    else
                    {
                        Infragistics.Documents.Excel.WorksheetRow dataRow = row;

                        if (dataRow.Cells[0].Value == null && dataRow.Cells[columnCount - 1].Value == null)
                        {
                            break;
                        }

                        DataRow dr = dt.NewRow();

                        for (Int32 j = 0; j < columnCount; j++)
                        {
                            dr[j] = dataRow.Cells[j].Value;
                        }

                        dt.Rows.Add(dr);
                    }
                    RowNum = RowNum + 1;
                }
            }

            dsExcel.Tables.Add(dt);

            return(dsExcel);
        }
예제 #2
0
 private void radButton2_Click(object sender, EventArgs e)
 {
     // ultraSpreadsheet1.ActiveCell.Column + 1;
     Infragistics.Documents.Excel.Worksheet sheet = ultraSpreadsheet1.ActiveWorksheet;
     foreach (var item in sheet.Columns)
     {
     }
 }
        private void btnImportExcel_Click(object sender, EventArgs e)
        {
            int           Value        = 0;
            bool          Imported     = true;
            int           ErrorCounter = 0;
            DateTime      ValueDateTime;
            StringBuilder sbMensaje = new StringBuilder();

            //if (_TempPacientList == null) return;
            if (_TempPacientList != null)
            {
                if (MessageBox.Show("Ya existe una lista de pacientes por agendar; ¿Desea reemplazarla?.", "Pregunta", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.No)
                {
                    return;
                }
            }
            openFileDialog1.FileName = string.Empty;
            openFileDialog1.Filter   = "Image Files (*.xls;*.xlsx)|*.xls;*.xlsx";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                _TempPacientList = new List <PacientList>();

                var Ext = Path.GetExtension(openFileDialog1.FileName).ToUpper();

                //try
                //{
                if (Ext == ".XLSX" || Ext == ".XLS")
                {
                    Infragistics.Documents.Excel.Workbook workbook1 = Infragistics.Documents.Excel.Workbook.Load(openFileDialog1.FileName);

                    Infragistics.Documents.Excel.Worksheet worksheet1 = workbook1.Worksheets["PLANTILLA"];

                    PacientList TempPacient;

                    int i  = 4;
                    int ii = 4;
                    //Validar que el excel no esta vacio
                    while (worksheet1.Rows[ii].Cells[0].Value != null)
                    {
                        if (worksheet1.Rows[ii].Cells[0].Value == null || worksheet1.Rows[ii].Cells[1].Value == null || worksheet1.Rows[ii].Cells[2].Value == null || worksheet1.Rows[ii].Cells[3].Value == null || worksheet1.Rows[ii].Cells[4].Value == null || worksheet1.Rows[ii].Cells[5].Value == null || worksheet1.Rows[ii].Cells[6].Value == null || worksheet1.Rows[ii].Cells[7].Value == null || worksheet1.Rows[ii].Cells[8].Value == null || worksheet1.Rows[ii].Cells[9].Value == null || worksheet1.Rows[ii].Cells[10].Value == null || worksheet1.Rows[ii].Cells[11].Value == null || worksheet1.Rows[ii].Cells[12].Value == null)
                        {
                            for (int y = 0; y <= 12; y++)
                            {
                                if (worksheet1.Rows[ii].Cells[y].Value == null)
                                {
                                    Imported = false;
                                    sbMensaje.Append("Registro número : ");
                                    sbMensaje.Append(worksheet1.Rows[ii].Cells[0].Value);
                                    sbMensaje.Append(". El campo " + worksheet1.Rows[3].Cells[y].Value.ToString() + " no puede estar vacio");
                                    sbMensaje.Append("\n");
                                }
                            }
                        }
                        ii++;
                    }
                    if (Imported == false)
                    {
                        MessageBox.Show(sbMensaje.ToString(), "Corregir registros en blanco", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return;
                    }


                    while (worksheet1.Rows[i].Cells[0].Value != null)
                    {
                        TempPacient = new PacientList();

                        if (worksheet1.Rows[i].Cells[0].Value != null)
                        {
                            TempPacient.i_Correlative = int.Parse(worksheet1.Rows[i].Cells[0].Value.ToString());
                            Imported = true;
                        }
                        //Nombres
                        if (worksheet1.Rows[i].Cells[1].Value != null)
                        {
                            TempPacient.v_FirstName = worksheet1.Rows[i].Cells[1].Value.ToString();
                            Imported = true;
                        }
                        else
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo Nombres es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        //Apellido Paterno
                        if (worksheet1.Rows[i].Cells[2].Value != null)
                        {
                            TempPacient.v_FirstLastName = worksheet1.Rows[i].Cells[2].Value.ToString();
                            Imported = true;
                        }
                        else
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo Apellido Paterno es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        //Apellido Materno
                        if (worksheet1.Rows[i].Cells[3].Value != null)
                        {
                            TempPacient.v_SecondLastName = worksheet1.Rows[i].Cells[3].Value.ToString();
                            Imported = true;
                        }
                        else
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo Apellido Materno es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        //ID Tipo Documento
                        if (worksheet1.Rows[i].Cells[4].Value == null)
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo ID Tipo Documento es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        if (int.TryParse(worksheet1.Rows[i].Cells[4].Value.ToString(), out Value) == false)
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo ID Tipo Documento es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        else
                        {
                            Imported = true;
                            TempPacient.i_DocTypeId = int.Parse(worksheet1.Rows[i].Cells[4].Value.ToString());
                        }
                        //Nombre Tipo Documento
                        if (worksheet1.Rows[i].Cells[5].Value != null)
                        {
                            TempPacient.v_DocTypeName = worksheet1.Rows[i].Cells[5].Value.ToString();
                            Imported = true;
                        }
                        else
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo Nombre Tipo Documento es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        //Número Documento
                        if (worksheet1.Rows[i].Cells[6].Value != null)
                        {
                            if (worksheet1.Rows[i].Cells[4].Value.ToString() == "1") // DNI
                            {
                                if (worksheet1.Rows[i].Cells[6].Value.ToString().Length != 8)
                                {
                                    ErrorCounter++;
                                    Imported = false;
                                    sbMensaje.Append("Registro número : ");
                                    sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                                    sbMensaje.Append(". El campo Número de DNI debe tener 8 dígitos");
                                    sbMensaje.Append("\n");
                                    i++;
                                    continue;
                                }
                                else
                                {
                                    Imported = true;
                                    TempPacient.v_DocNumber = worksheet1.Rows[i].Cells[6].Value.ToString();
                                }
                            }
                            else if (worksheet1.Rows[i].Cells[4].Value.ToString() == "2") // PASAPORTE
                            {
                                if (worksheet1.Rows[i].Cells[6].Value.ToString().Length != 9)
                                {
                                    ErrorCounter++;
                                    Imported = false;
                                    sbMensaje.Append("Registro número : ");
                                    sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                                    sbMensaje.Append(". El Número PASAPORTE debe tener 9 dígitos");
                                    sbMensaje.Append("\n");
                                    i++;
                                    continue;
                                }
                                else
                                {
                                    Imported = true;
                                    TempPacient.v_DocNumber = worksheet1.Rows[i].Cells[6].Value.ToString();
                                }
                            }
                            else if (worksheet1.Rows[i].Cells[4].Value.ToString() == "3") // LICENCIA DE CONDUCIR
                            {
                                if (worksheet1.Rows[i].Cells[6].Value.ToString().Length != 10)
                                {
                                    ErrorCounter++;
                                    Imported = false;
                                    sbMensaje.Append("Registro número : ");
                                    sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                                    sbMensaje.Append(". El Número LICENCIA DE CONDUCIR debe tener 10 dígitos");
                                    sbMensaje.Append("\n");
                                    i++;
                                    continue;
                                }
                                else
                                {
                                    Imported = true;
                                    TempPacient.v_DocNumber = worksheet1.Rows[i].Cells[6].Value.ToString();
                                }
                            }
                            else if (worksheet1.Rows[i].Cells[4].Value.ToString() == "4")// CARNET DE EXTRANJERIA
                            {
                                if (worksheet1.Rows[i].Cells[6].Value.ToString().Length != 11)
                                {
                                    ErrorCounter++;
                                    Imported = false;
                                    sbMensaje.Append("Registro número : ");
                                    sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                                    sbMensaje.Append(". El Número CARNET DE EXTRANJERIA debe tener 11 dígitos");
                                    sbMensaje.Append("\n");
                                    i++;
                                    continue;
                                }
                                else
                                {
                                    Imported = true;
                                    TempPacient.v_DocNumber = worksheet1.Rows[i].Cells[6].Value.ToString();
                                }
                            }
                        }
                        else
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo Número Documento es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        //ID Género
                        if (int.TryParse(worksheet1.Rows[i].Cells[7].Value.ToString(), out Value))
                        {
                            Imported = true;
                            TempPacient.i_SexTypeId = int.Parse(worksheet1.Rows[i].Cells[7].Value.ToString());
                        }
                        else
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo ID Género es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        // Nombre Género
                        if (worksheet1.Rows[i].Cells[8].Value != null)
                        {
                            Imported = true;
                            TempPacient.v_SexTypeName = worksheet1.Rows[i].Cells[8].Value.ToString();
                        }
                        else
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo Nombre Género es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        //Fecha Nacimiento
                        if (worksheet1.Rows[i].Cells[12].Value == null)
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo Fecha Nacimiento es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        if (DateTime.TryParseExact(worksheet1.Rows[i].Cells[12].Value.ToString(), "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out ValueDateTime) == false)
                        {
                            ErrorCounter++;
                            Imported = false;
                            sbMensaje.Append("Registro número : ");
                            sbMensaje.Append(worksheet1.Rows[i].Cells[0].Value);
                            sbMensaje.Append(". El campo Fecha Nacimiento es inválido");
                            sbMensaje.Append("\n");
                            i++;
                            continue;
                        }
                        else
                        {
                            Imported = true;
                            TempPacient.d_Birthdate = DateTime.ParseExact(worksheet1.Rows[i].Cells[12].Value.ToString(), "yyyyMMdd", CultureInfo.InvariantCulture);
                        }


                        //Puesto de Trabajo
                        if (worksheet1.Rows[i].Cells[13].Value != null)
                        {
                            TempPacient.v_CurrentOccupation = worksheet1.Rows[i].Cells[13].Value.ToString();
                            Imported = true;
                        }
                        else
                        {
                            //ErrorCounter++;
                            //Imported = false;
                            //sbMensaje.Append("Registro número : ");
                            //sbMensaje.Append(worksheet1.Rows[i].Cells[10].Value);
                            //sbMensaje.Append(". El campo Nombres es inválido");
                            //sbMensaje.Append("\n");
                            //i++;
                            //continue;
                            TempPacient.v_CurrentOccupation = string.Empty;
                        }

                        //PROTOCOLO ID
                        if (worksheet1.Rows[i].Cells[14].Value != null)
                        {
                            TempPacient.v_ProtocoloId = worksheet1.Rows[i].Cells[14].Value.ToString();
                            Imported = true;
                        }
                        else
                        {
                            //ErrorCounter++;
                            //Imported = false;
                            //sbMensaje.Append("Registro número : ");
                            //sbMensaje.Append(worksheet1.Rows[i].Cells[10].Value);
                            //sbMensaje.Append(". El campo Nombres es inválido");
                            //sbMensaje.Append("\n");
                            //i++;
                            //continue;
                            TempPacient.v_ProtocoloId = string.Empty;
                        }
                        _TempPacientList.Add(TempPacient);

                        var Result = _TempPacientList.FindAll(p => p.v_DocNumber == TempPacient.v_DocNumber && p.i_DocTypeId == TempPacient.i_DocTypeId);
                        if (Result.Count > 1)
                        {
                            MessageBox.Show("El correlativo " + Result[0].i_Correlative + " tiene el mismo Número Documento que el correlativo " + Result[1].i_Correlative + " .Revise el Excel y corriga la duplicidad", "Error al cargar Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            return;
                        }
                        i++;
                    }

                    lblRecordCountPacients.Text = string.Format("Se encontraron {0} registros.", _TempPacientList.Count());

                    if (ErrorCounter > 0)
                    {
                        _TempPacientList         = new List <PacientList>();
                        grdDataPeople.DataSource = new List <PacientList>();
                        MessageBox.Show(sbMensaje.ToString(), "Registros no importados", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        sbMensaje = null;
                    }
                    else if (ErrorCounter == 0)
                    {
                        grdDataPeople.DataSource = _TempPacientList;
                        MessageBox.Show("Se importaron " + _TempPacientList.Count() + " registros.", "Importación correcta", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        _TempPacientList         = new List <PacientList>();
                        grdDataPeople.DataSource = new List <PacientList>();
                        MessageBox.Show(sbMensaje.ToString(), "Registros no importados", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        sbMensaje = null;
                    }
                }
                else
                {
                    grdDataPeople.DataSource = new List <PacientList>();
                    MessageBox.Show("Seleccione un formato correcto (.xlsx)", "INFORMACIÓN", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                //}
                //catch (Exception )
                //{
                //    MessageBox.Show("El archivo está en uso. Por favor cierra el documento.", "INFORMACIÓN", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                //}
            }
        }
        private void ExportData()
        {
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            DateTime       FromDate        = DateTime.Parse(dtFromDate.Value.ToString());
            DateTime       ToDate          = DateTime.Parse(dtToDate.Value.ToString());

            saveFileDialog1.DefaultExt       = "txt";
            saveFileDialog1.Filter           = "ExcelFile |*.xlsx";
            saveFileDialog1.AddExtension     = true;
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.Title            = "Bạn cần lưu file ở đâu?";
            saveFileDialog1.InitialDirectory = @"C:/";
            string datetime = string.Format("Tử ngày {0} đến ngày {1}", FromDate.ToString("dd/MM/yyyy"), ToDate.ToString("dd/MM/yyyy"));

            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                var      row      = Session.CompanyInfo;
                string[] lstTitle = { String.Format("{0} - {1}", row.Name, row.Address), "BÁO CÁO DOANH THU THEO NHÂN VIÊN", datetime };
                Infragistics.Documents.Excel.Workbook workbook = new Infragistics.Documents.Excel.Workbook();
                if (Path.GetExtension(saveFileDialog1.FileName) == ".xlsx")
                {
                    workbook.SetCurrentFormat(WorkbookFormat.Excel2007);
                }
                workbook.Culture = CultureInfo.CurrentCulture;
                Infragistics.Documents.Excel.Worksheet SheetExcel = null;
                string sGroupSeparator           = workbook.Culture.NumberFormat.CurrencyGroupSeparator;
                string sDecimalSeparator         = workbook.Culture.NumberFormat.CurrencyDecimalSeparator;
                List <UltraGridColumn> lstColumn = new List <UltraGridColumn>();
                UltraGridColumn        clColumn  = grdList.DisplayLayout.Bands[0].GetFirstVisibleCol(grdList.ActiveColScrollRegion, true);
                while (clColumn != null)
                {
                    lstColumn.Add(clColumn);
                    clColumn = clColumn.GetRelatedVisibleColumn(VisibleRelation.Next);
                }
                List <UltraGridColumn> lstGroup = new List <UltraGridColumn>();
                foreach (UltraGridColumn gridColumnGroup in grdList.DisplayLayout.Bands[0].SortedColumns)
                {
                    if (gridColumnGroup.IsGroupByColumn)
                    {
                        lstGroup.Add(gridColumnGroup);
                    }
                }
                SheetExcel = workbook.Worksheets.Add("Bao_cao_doanh_thu");
                int iHeaderPosition = lstColumn.Count;
                for (int i = 0; i <= lstTitle.Length - 1; i++)
                {
                    string sTitle = lstTitle[i];
                    {
                        SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Alignment = HorizontalCellAlignment.Center;
                        SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
                        SheetExcel.Rows[i].Cells[iHeaderPosition / 2].Value = sTitle;

                        SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Font.Name         = "";
                        SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.BottomBorderStyle = CellBorderLineStyle.None;
                        SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.TopBorderStyle    = CellBorderLineStyle.None;
                        SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.LeftBorderStyle   = CellBorderLineStyle.None;
                        SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.RightBorderStyle  = CellBorderLineStyle.None;
                        if (i == 0)
                        {
                            SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Font.Height = 280;
                            SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Alignment   = HorizontalCellAlignment.Left;
                        }
                        else if (i == 1)
                        {
                            SheetExcel.Rows[i].Cells[iHeaderPosition / 2].CellFormat.Font.Height = 380;
                        }
                    }
                    SheetExcel.MergedCellsRegions.Add(i, 0, i, iHeaderPosition - 1);
                }
                Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter ultraGridExcelExporter = new Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter();
                ultraGridExcelExporter.BandSpacing = Infragistics.Win.UltraWinGrid.ExcelExport.BandSpacing.None;
                ultraGridExcelExporter.Export(grdList, SheetExcel, lstTitle.Length, 0);
                for (int i = 0; i < lstGroup.Count - 1; i += 1)
                {
                    SheetExcel.Columns[i].SetWidth(10, WorksheetColumnWidthUnit.Pixel);
                }
                for (int i = lstGroup.Count; i <= lstGroup.Count + lstColumn.Count - 1; i += 1)
                {
                    SheetExcel.Columns[i].SetWidth(lstColumn[i - lstGroup.Count].Width, WorksheetColumnWidthUnit.Pixel);
                    if (lstColumn[i - lstGroup.Count].DataType == typeof(DateTime))
                    {
                        SheetExcel.Columns[i].CellFormat.FormatString = "dd/MM/yyyy";
                    }
                    else if (lstColumn[i - lstGroup.Count].DataType == typeof(double) || lstColumn[i - lstGroup.Count].DataType == typeof(decimal) || lstColumn[i - lstGroup.Count].DataType == typeof(int) || lstColumn[i - lstGroup.Count].DataType == typeof(Int16) || lstColumn[i - lstGroup.Count].DataType == typeof(Int32) || lstColumn[i - lstGroup.Count].DataType == typeof(Int64))
                    {
                        string sFormat       = lstColumn[i - lstGroup.Count].Format == null ? "" : lstColumn[i - lstGroup.Count].Format;
                        int    iDecimalDigit = 0;
                        if ((sFormat.StartsWith("N") || sFormat.StartsWith("C")) && int.TryParse(sFormat.Substring(1), out iDecimalDigit))
                        {
                            if (iDecimalDigit > 0)
                            {
                                SheetExcel.Columns[i].CellFormat.FormatString = string.Format(FormatString, sGroupSeparator, sDecimalSeparator, new string('0', iDecimalDigit));
                            }
                            else
                            {
                                SheetExcel.Columns[i].CellFormat.FormatString = string.Format(FormatString, sGroupSeparator, string.Empty, string.Empty);
                            }
                        }
                        else
                        {
                            SheetExcel.Columns[i].CellFormat.FormatString = "0";
                        }
                    }
                }
                workbook.Save(saveFileDialog1.FileName);
            }
            else
            {
            }
            saveFileDialog1.Dispose();
            saveFileDialog1 = null;
        }
    public void formatExcel(DataSet ds, DataSet d, string strCultureCode)
    {
        Infragistics.Documents.Excel.Workbook workbook = new Infragistics.Documents.Excel.Workbook();
        int rowIndex;

        foreach (DataTable dt in ds.Tables)
        {
            Infragistics.Documents.Excel.Worksheet worksheet = workbook.Worksheets.Add(dt.TableName);
            DataRowCollection tableRows = d.Tables[0].Rows;
            for (int colindex = 0; colindex < dt.Columns.Count; colindex++)
            {
                worksheet.Columns[colindex].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
                #region Column Section
                worksheet.Rows[0].Cells[colindex].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
                worksheet.Rows[0].Cells[colindex].CellFormat.Alignment = HorizontalCellAlignment.Center;
                worksheet.Rows[0].Cells[0].Value = dt.Columns[0].ColumnName;
                worksheet.Rows[0].Cells[1].Value = dt.Columns[1].ColumnName;
                worksheet.Rows[0].Cells[2].Value = dt.Columns[2].ColumnName;
                worksheet.Rows[0].Cells[3].Value = dt.Columns[3].ColumnName;
                worksheet.Rows[0].Cells[4].Value = dt.Columns[4].ColumnName;
                worksheet.Rows[0].Cells[5].Value = dt.Columns[5].ColumnName;
                worksheet.Rows[0].Cells[6].Value = dt.Columns[6].ColumnName;
                worksheet.Rows[0].Cells[7].Value = dt.Columns[7].ColumnName;
                worksheet.Rows[0].Cells[8].Value = dt.Columns[8].ColumnName;
                worksheet.Rows[0].Cells[9].Value = dt.Columns[9].ColumnName;
                #endregion
            }
            #region Row Section
            rowIndex = 1;
            foreach (DataRow dr in dt.Rows)
            {
                worksheet.Rows[rowIndex].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
                Infragistics.Documents.Excel.WorksheetRow row = worksheet.Rows[rowIndex++];

                for (int colIndex = 0; colIndex < dr.ItemArray.Length; colIndex++)
                {
                    row.Cells[colIndex].Value = dr.ItemArray[colIndex];
                }
            }
            #endregion
        }
        string            fileName       = string.Empty;
        string            formatFileName = DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.ToString("HHmmss");
        DataRowCollection tableRow       = d.Tables[0].Rows;
        foreach (DataRow row in tableRow)
        {
            string hostName = row[0].ToString();
            fileName += "CASLinkExport-" + hostName + "-" + strCultureCode + "-" + SessionState.User.Id.ToString() + "-" + formatFileName + ".xls";
        }
        workbook.ActiveWorksheet = workbook.Worksheets[0];
        MemoryStream stream = new MemoryStream();
        BIFF8Writer.WriteWorkbookToStream(workbook, stream);
        Response.Clear();
        Response.ClearContent();
        Response.ClearHeaders();
        Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
        Response.ContentType     = "application/ms-excel";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.AppendHeader("Content-Length", stream.Length.ToString());
        EnableViewState = false;
        Response.OutputStream.Write(stream.ToArray(), 0, Convert.ToInt32(stream.Length));
        Response.End();
    }