NPOI

 

//读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;
}