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