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