//读excel数据到data中

public static DataTable ReadXlsxData(Stream fileStream, string sheetName)

{

try

{

XSSFWorkbook hssfworkbook = new XSSFWorkbook(fileStream);

var sheet = hssfworkbook.GetSheet(sheetName);

IEnumerator rows = sheet.GetRowEnumerator();

DataTable dataTable = new DataTable(sheetName);

while (rows.MoveNext())

{

if (dataTable.Columns.Count == 0)

{

IRow row = (XSSFRow)rows.Current;

int y = 0;

while (true)

{

ICell cell = row.GetCell(y++);

if ((cell == null) || (string.IsNullOrEmpty(cell.ToString())))

{

break;

}

dataTable.Columns.Add(cell.ToString());

}

}

else

{

IRow row = (XSSFRow)rows.Current;

DataRow dr = dataTable.NewRow();

for (int i = 0; i < dataTable.Columns.Count; i++)

{

ICell cell = row.GetCell(i);

if (cell == null)

{

dr[i] = null;

}

else

{

dr[i] = cell.ToString();

}

}

dataTable.Rows.Add(dr);

}

}

return dataTable;

}

catch

{

throw;

}

}

 

//导出excel

public void Export()

{

MemoryStream ms = RateDTToMS(dt);

string fileName = commonContext.GetExcelName(ExcelName);

if (Request.Browser.Browser == "IE")

fileName = HttpUtility.UrlEncode(fileName);

Response.Clear();

Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);

Response.BinaryWrite(ms.ToArray());

Response.End();

}

 

/// <summary>

/// datatable 转换成MemoryStream

/// </summary>

/// <param name="source">datatable</param>

/// <returns>MemoryStream</returns>

public MemoryStream RateDTToMS(DataTable source)

{

MemoryStream memoryStream = new MemoryStream();

IWorkbook workbook = new XSSFWorkbook();

ISheet sheet = workbook.CreateSheet();

IRow headerRow = sheet.CreateRow(0);

int indexDeptName = 0;

int indexModule = 1;

int indexCatType = 2;

int indexOwner = 3;

int indexJobName = 4;

int indexJobCnName = 5;

int indexJobCount = 6;

int indexAllCount = 7;

int indexRate = 8;

// handling header.

headerRow.CreateCell(indexDeptName).SetCellValue("归属部门");

headerRow.CreateCell(indexModule).SetCellValue("归属模块");

headerRow.CreateCell(indexCatType).SetCellValue("归属功能");

headerRow.CreateCell(indexOwner).SetCellValue("负责人");

headerRow.CreateCell(indexRate).SetCellValue("占比(%)");

// handling value.

int rowIndex = 1;

foreach (DataRow dr in source.Rows)

{

IRow dataRow = sheet.CreateRow(rowIndex);

dataRow.CreateCell(indexDeptName).SetCellValue(dr["DepartmentName"].ToString());

dataRow.CreateCell(indexModule).SetCellValue(dr["ModuleName"].ToString());

dataRow.CreateCell(indexCatType).SetCellValue(dr["CattypeName"].ToString());

dataRow.CreateCell(indexOwner).SetCellValue(dr["FJCOwner"].ToString());

dataRow.CreateCell(indexRate).SetCellValue(rate);

rowIndex++;

}

workbook.Write(memoryStream);

memoryStream.Flush();

return memoryStream;

}