Question

NPOI excel 导入导出数据，求救

NPOI  excel 导入导出数据，.csv 文件报错 Unable to Read entire header; 30 bytes Read; expected 512 bytes 。如果是 office 07 也报错 说不支持 The supplied data appears to be in the Office 2007+ XML. POI only supports OLE2 Office documents ， Stream ExcelFileStream HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); 时报错

Answer 1

给你一个核心代码 下面的FOR循环只是填充数据而已
PageList<FairBatchOrderNo> list = orderBll.GetFairServiceOrderNoListToExcel(pageIndex, pageSize, batchID, postersState, companyID, companyName, creator, auditor, beginDate, endDate, beginCreateDate, endCreateDate, beginAuditDate, endAuditDate, needProcess, fairServiceBatchState, needPrint);
try
{
HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.CreateSheet("现场招聘服务统计");
HSSFCellStyle cellStyle = book.CreateCellStyle();
cellStyle.BorderTop = CellBorderType.THIN;
cellStyle.BorderBottom = CellBorderType.THIN;
cellStyle.BorderLeft = CellBorderType.THIN;
cellStyle.BorderRight = CellBorderType.THIN;

HSSFRow headerRow = sheet.CreateRow(0);
HSSFCell headerCell = headerRow.CreateCell(0);

sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(0, 0, 0, 5));
HSSFCellStyle headerStyle = book.CreateCellStyle();
headerStyle.Alignment = CellHorizontalAlignment.CENTER;
headerCell.CellStyle = headerStyle;

//设置表头
{
HSSFRow row = sheet.CreateRow(1);
HSSFCell rowIndexCell = row.CreateCell(0);
rowIndexCell.CellStyle = cellStyle;
HSSFCell companyIDCell = row.CreateCell(1);
companyIDCell.CellStyle = cellStyle;
HSSFCell companyNameCell = row.CreateCell(2);
companyNameCell.CellStyle = cellStyle;
//HSSFCell batchNameCell = row.CreateCell(3);
//batchNameCell.CellStyle = cellStyle;
HSSFCell linkManCell = row.CreateCell(3);
linkManCell.CellStyle = cellStyle;
HSSFCell linkByCell = row.CreateCell(4);
linkByCell.CellStyle = cellStyle;
HSSFCell deptCell = row.CreateCell(5);
deptCell.CellStyle = cellStyle;
HSSFCell saleManCell = row.CreateCell(6);
saleManCell.CellStyle = cellStyle;
HSSFCell createTimeCell = row.CreateCell(7);
createTimeCell.CellStyle = cellStyle;
HSSFCell FSTStateCell = row.CreateCell(8);
FSTStateCell.CellStyle = cellStyle;
HSSFCell isCollectionCell = row.CreateCell(9);
isCollectionCell.CellStyle = cellStyle;
HSSFCell trueCollGoldCell = row.CreateCell(10);
trueCollGoldCell.CellStyle = cellStyle;

rowIndexCell.SetCellValue("序号");
companyIDCell.SetCellValue("公司序号");
companyNameCell.SetCellValue("公司名称");
//batchNameCell.SetCellValue("参会名称");
linkManCell.SetCellValue("联系人");
linkByCell.SetCellValue("联系电话");
deptCell.SetCellValue("部门");
saleManCell.SetCellValue("销售人员");
createTimeCell.SetCellValue("添加时间");
FSTStateCell.SetCellValue("场次状态");
isCollectionCell.SetCellValue("是否收款");
trueCollGoldCell.SetCellValue("现场参会应收金额");
}

int serviceBatchNoOpen = 0;
int serviceBatchProcess = 0;
int serviceBatchStop = 0;

for (int i = 0; i < list.Count; i++)
{
HSSFRow row = sheet.CreateRow(i + 2);

HSSFCell rowIndexCell = row.CreateCell(0);
rowIndexCell.CellStyle = cellStyle;
HSSFCell companyIDCell = row.CreateCell(1);
companyIDCell.CellStyle = cellStyle;
HSSFCell companyNameCell = row.CreateCell(2);
companyNameCell.CellStyle = cellStyle;
//HSSFCell batchNameCell = row.CreateCell(3);
//batchNameCell.CellStyle = cellStyle;
HSSFCell linkManCell = row.CreateCell(3);
linkManCell.CellStyle = cellStyle;
HSSFCell linkByCell = row.CreateCell(4);
linkByCell.CellStyle = cellStyle;
HSSFCell deptCell = row.CreateCell(5);
deptCell.CellStyle = cellStyle;
HSSFCell saleManCell = row.CreateCell(6);
saleManCell.CellStyle = cellStyle;
HSSFCell createTimeCell = row.CreateCell(7);
createTimeCell.CellStyle = cellStyle;
HSSFCell FSTStateCell = row.CreateCell(8);
FSTStateCell.CellStyle = cellStyle;
HSSFCell isCollectionCell = row.CreateCell(9);
isCollectionCell.CellStyle = cellStyle;
HSSFCell trueCollGoldCell = row.CreateCell(10);
trueCollGoldCell.CellStyle = cellStyle;

FairBatchOrderNo order = list[i];

FSTStateCell.SetCellValue(EnumUtils.GetName(order.OrderNoState));
JujiaoOA.Model.CRM.Company company = companyBll.GetCompany(order.CompanyID);
if (company != null)
{
rowIndexCell.SetCellValue(order.OrderNo);
companyIDCell.SetCellValue(company.CompanyID);
companyNameCell.SetCellValue(company.CompanyName);

if (company.IsLocked)
{
UserMapping map = userMappingBll.GetUserMappingByOuterID(company.LockMan);
if (map != null)
{
JujiaoOA.Model.Sys_new.User user = userBll.GetUser(map.UserID);
if (user != null)
{
saleManCell.SetCellValue(user.UserName);
Department dept = deptBll.GetDepartment(user.DeptId);
if (dept != null)
{
deptCell.SetCellValue(dept.DeptName);
}
}
}
}
}

//判断是否已删除，如果已删除，说明fairServiceBatch不存在数据了，就不用进行下面一些步骤
if (order.OrderNoState == FairBatchOrderNoState.Deleted)
{
linkManCell.SetCellValue("");
linkByCell.SetCellValue("");
createTimeCell.SetCellValue("");
isCollectionCell.SetCellValue("");
trueCollGoldCell.SetCellValue("");
}
else
{
FairServiceBatch batch = batchBll.GetFairServiceBatch(order.FairServiceBatchID);
if (batch != null)
{
createTimeCell.SetCellValue(batch.CreatorTime.ToString());

Service service = serviceBll.GetService(batch.ServiceID);
if (service != null)
{
decimal funds = 0.00m;
decimal PayedFee = 0.00m;
List<Performance> performanceList = performanceBll.GetPerFormanceByBusinessID(service.BusinessID);
int j = 0;
foreach (Performance performance in performanceList)
{
j++;
if (performance != null)
{
funds += fundsBll.GetFundsFeeSumByPerf(performance.PerformanceID);
PayedFee += performance.NeedPay;
if (j == 1)
{
linkManCell.SetCellValue(performance.LinkMan);
linkByCell.SetCellValue(performance.LinkBy);
}
}
else
{
if(j==1)
{
linkManCell.SetCellValue("");
linkByCell.SetCellValue("");
isCollectionCell.SetCellValue("");
trueCollGoldCell.SetCellValue("");
}
}
}

isCollectionCell.SetCellValue(PayedFee - funds <= 0 ? "是" : "否");
string strUseType = (PayedFee - funds).ToString();

switch (service.UseType)
{
case ServiceUseType.Normal:
{
if (PayedFee <= 0)
{
strUseType = "收款异常";
}
else
{
if ((funds - PayedFee) >= 0)
{
strUseType = "已收款";
}
}
break;
}
}
if (batch.State == FairServiceBatchState.Stoped)
{
isCollectionCell.SetCellValue("是");
strUseType = "无需收款";
trueCollGoldCell.SetCellValue(strUseType);
}
else
{
try
{
trueCollGoldCell.SetCellValue(Convert.ToDouble(strUseType));
}
catch
{
trueCollGoldCell.SetCellValue(strUseType);
}
}
}

if (batch.State == FairServiceBatchState.Processed)
{
serviceBatchProcess++;
}
else if (batch.State == FairServiceBatchState.Stoped)
{
serviceBatchStop++;
}
else
{
serviceBatchNoOpen++;
}

}
}
}
headerCell.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "现场招聘服务统计(场次总数：" + list.Count.ToString() + "，未开通："+serviceBatchNoOpen+"，开通："+serviceBatchProcess+"，停止："+serviceBatchStop+")");
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=" + this.Server.UrlEncode(string.Format("{0:yyyy年MM月}.xls", DateTime.Now.ToString("yyyy-MM"))));
this.EnableViewState = false;
book.Write(Response.OutputStream);
}
catch (Exception ex)
{
log.Error("导出Excel文件失败！", ex);
MessageBox.Error("导出Excel文件失败！", -1);
return;
}

Answer 2

呆呆的说：“……嗯，是块好东西！纯金的护身符，能不行吗，比你上次的护身符还贵重。阿龙，你小子越来越讲究了。”