/// <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);
}
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();
}
