public FileResult ExportOverview()
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Overview");
//获取list数据
var overview = repo.All().ToList();
//List<TB_STUDENTINFOModel> listRainInfo = m_BLL.GetSchoolListAATQ(schoolname);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("客戶名稱");
row1.CreateCell(1).SetCellValue("聯絡人數量");
row1.CreateCell(2).SetCellValue("銀行帳戶數量");
//将数据逐步写入sheet1各个行
for (int i = 0; i < overview.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(overview[i].客戶名稱.ToString());
rowtemp.CreateCell(1).SetCellValue(overview[i].聯絡人數量.ToString());
rowtemp.CreateCell(2).SetCellValue(overview[i].銀行帳戶數量.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "CustomersOverView.xls"));
}
public FileResult ExportData()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("客戶名稱");
row1.CreateCell(1).SetCellValue("聯絡人數量");
row1.CreateCell(2).SetCellValue("銀行帳戶數量");
var i = 0;
foreach (var item in db.CustomView.AsEnumerable())
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(item.客戶名稱);
rowtemp.CreateCell(1).SetCellValue(item.聯絡人數量.ToString());
rowtemp.CreateCell(2).SetCellValue(item.銀行帳戶數量.ToString());
i++;
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "ReadCustomView.xls"));
}
/// <summary>
/// 获取不动产的办件步骤信息
/// </summary>
/// <returns></returns>
public void GetBdcStepInfo(string fileName)
{
BDCInfo4WWWDal dal = new BDCInfo4WWWDal();
DataTable dt = dal.GetBDCStepInfo();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("不动产办理步骤信息");
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("业务编号");
row.CreateCell(1).SetCellValue("查询密码");
row.CreateCell(2).SetCellValue("通知人");
row.CreateCell(3).SetCellValue("流程步骤");
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(i + 1);
row1.CreateCell(0).SetCellValue(dt.Rows[i]["SLBH"].ToString());
row1.CreateCell(1).SetCellValue(dt.Rows[i]["CXMM"].ToString());
row1.CreateCell(2).SetCellValue(dt.Rows[i]["TZRXM"].ToString());
row1.CreateCell(3).SetCellValue(dt.Rows[i]["StepName"].ToString());
}
}
using (FileStream stm = File.OpenWrite(fileName))
{
book.Write(stm);
}
}
public static void FullSheet(NPOI.SS.UserModel.ISheet iSheet, System.Data.DataTable dataTable, bool firstRowIsColumnHead)
{
if (iSheet == null)
{
throw new AggregateException("参数NPOI.SS.UserModel.ISheet对象为null。");
}
NPOI.SS.UserModel.IRow iRow = iSheet.CreateRow(0);
for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
{
NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex);
iCell.SetCellValue(dataTable.Columns[dataTableColumnIndex].ColumnName);
}
int startRowIndex = 0;
if (firstRowIsColumnHead)
{
startRowIndex = 1;
}
for (int dataTableRowIndex = 0; dataTableRowIndex < dataTable.Rows.Count; dataTableRowIndex++)
{
iRow = iSheet.CreateRow(dataTableRowIndex + startRowIndex);
for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++)
{
NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex);
SetCellValue(iCell, dataTable.Rows[dataTableRowIndex][dataTableColumnIndex]);
}
}
}
/// <summary>
/// 把数据写入到工作表
/// </summary>
/// <param name="workbook"></param>
/// <param name="lst"></param>
/// <returns></returns>
public void InsertByColName(NPOI.SS.UserModel.ISheet sheet, IEnumerable <T> lst, int startrow = 0)
{
if (sheet == null)
{
throw new ArgumentException("sheet不能为Null");
}
if (lst == null)
{
throw new ArgumentException("value不能为Null");
}
if (dictMapHandlerByColName == null)
{
throw new ArgumentException("必须指定列名称和model之间的映射");
}
int colIndex = 0;
int rowIndex = startrow;
NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex++);
foreach (var kv in dictMapHandlerByColName)
{
row.CreateCell(colIndex++).SetCellValue(kv.Key);
}
foreach (var value in lst)
{
row = sheet.CreateRow(rowIndex++);
colIndex = 0;
foreach (var kv in dictMapHandlerByColName)
{
//dictMapHandler[kv.Value].SetValueToCell(value, row.CreateCell(kv.Key));
kv.Value(row.CreateCell(colIndex++), value);
}
}
}
public FileResult Export(string id)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //创建Excel文件的对象
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); //给sheet1添加第一行的头部标题
row1.CreateCell(0).SetCellValue("角色");
row1.CreateCell(1).SetCellValue("帐号");
row1.CreateCell(2).SetCellValue("姓名");
row1.CreateCell(3).SetCellValue("过期时间");
row1.CreateCell(4).SetCellValue("状态");
List <int> rids = new List <int>();
string[] ids = id.Split('_');
foreach (var d in ids)
{
if (!string.IsNullOrWhiteSpace(d))
{
rids.Add(int.Parse(d));
}
}
List <VModel.SyUserManager.Grid> list = Bll.SyUserBll.ExportByRoleIds(rids);
int i = 1;
foreach (var m in list)
{
NPOI.SS.UserModel.IRow r = sheet1.CreateRow(i);
if (m.RoleNames != null && m.RoleNames.Count > 0)
{
string roleName = "";
foreach (var name in m.RoleNames)
{
roleName += name + "，";
}
if (roleName != "")
{
roleName = roleName.Substring(0, roleName.Length - 1);
}
r.CreateCell(0).SetCellValue(roleName);
}
else
{
r.CreateCell(0).SetCellValue("");
}
r.CreateCell(1).SetCellValue(m.Account);
r.CreateCell(2).SetCellValue(m.Name);
r.CreateCell(3).SetCellValue(Common.Function.ConvertDate(m.ExpiresTime));
r.CreateCell(4).SetCellValue(Common.Dict.UserState.GetVal(m.IsEnabled));
i++;
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();// 写入到客户端
book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
string fileName = "角色包含用户表.xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public FileResult Export(int appId)
{
//获取list数据
var checkList = AppSettingBusiness.GetAppSettings(appId); //db.InfoTables.Where(r => r.ProjectName != null).Select(r => new { r.ProjectName, r.InfoTypes, r.field, r.fieldtxt }).ToList();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("配置键");
row1.CreateCell(1).SetCellValue("配置值");
//....N行
//将数据逐步写入sheet1各个行
for (int i = 0; i < checkList.Count(); i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(checkList[i].ConfigKey.ToString());
rowtemp.CreateCell(1).SetCellValue(checkList[i].ConfigValue.ToString());
//....N行
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
DateTime dt = DateTime.Now;
string dateTime = dt.ToString("yyMMddHHmmssfff");
string fileName = "配置" + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
public FileResult Export(string StoreId, string StartDate, string EndDate, int pi = 1)
{
using (DBContext db = new DBContext())
{
var storeQuery = db.Store.AsQueryable();
if (!string.IsNullOrEmpty(StoreId))
{
storeQuery = storeQuery.Where(q => q.ID.Equals(StoreId));
}
var orderQuery = db.Order.AsQueryable();
DateTime now = DateTime.Now;
//不选择开始日期默认为本月1号
DateTime start = string.IsNullOrEmpty(StartDate) ? DateTime.Parse(string.Format("{0}/{1}/{2}", now.Year.ToString(), now.Month.ToString(), "01")) : DateTime.Parse(StartDate);
DateTime end = string.IsNullOrEmpty(EndDate) ? now : DateTime.Parse(EndDate).AddDays(1);
if (start > end)
{
DateTime temp = DateTime.MinValue;
temp = end;
end = start;
start = temp;
}
orderQuery = orderQuery.Where(q => q.SubmitTime.CompareTo(start) > 0 && q.SubmitTime.CompareTo(end) < 0);
var list = (from q in storeQuery
join o in orderQuery on q.ID equals o.StoreId into o_join
from os in o_join.DefaultIfEmpty()
group new { q.StoreName, os.Paid } by new { q.StoreName } into s
select new PSJE()
{
StoreName = s.Key.StoreName, Pay = s.Sum(p => p.Paid == null ? 0 : p.Paid)
}).OrderByDescending(q => q.Pay).ToList();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("采购单位");
row1.CreateCell(1).SetCellValue("销售额");
string status = string.Empty;
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].StoreName);
rowtemp.CreateCell(1).SetCellValue(list[i].Pay.ToString());
}
// 写入到客户端
return(ExportExcel(book, now.ToString("yyMMddHHmmssfff")));
}
}
/// <summary>
/// DefectCode 导出
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
public FileResult DefectCodeExcel(DefectCodeSearchModel searchModel)
{
searchModel.PageSize = 1000;
var totalCount = 0;
var result = CodeBusiness.DefectCodeSearchResult(searchModel, out totalCount).ToList();
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("Code Type");
row1.CreateCell(1).SetCellValue("No");
row1.CreateCell(2).SetCellValue("Code No");
row1.CreateCell(3).SetCellValue("Code Name(English)");
row1.CreateCell(4).SetCellValue("Code Name(Chinese)");
for (int i = 0; i < result.Count(); i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(result[i].BDCodeType);
rowtemp.CreateCell(1).SetCellValue(result[i].BDCodeNo);
rowtemp.CreateCell(2).SetCellValue(result[i].BDCode);
rowtemp.CreateCell(3).SetCellValue(result[i].BDCodeNameEn);
rowtemp.CreateCell(4).SetCellValue(result[i].BDCodeNameCn);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
var exportFileName = string.Format("{0}{1}.xls", "DefectCodeInfo", DateTime.Now.ToString("yyyyMMddHHmmss"));
return(File(ms, "application/vnd.ms-excel", exportFileName));
}
public FileResult ExportExcelForChargeSSR(List <StdSumReport> list)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("时间");
row1.CreateCell(1).SetCellValue("使用气量");
//row1.CreateCell(2).SetCellValue("充值金额");
//将数据逐步写入sheet1各个行
//var list = new List<FMModel>();
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].dt.ToString());
rowtemp.CreateCell(1).SetCellValue(list[i].span.ToString());
//rowtemp.CreateCell(2).SetCellValue(list[i].sumMoney.ToString());
}
MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string dateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string fileName = "用量统计查询" + dateTime + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public FileResult getFile()
{
List <Person> list = Option.getAwardInfo();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("qq");
row1.CreateCell(1).SetCellValue("昵称");
row1.CreateCell(2).SetCellValue("奖品");
row1.CreateCell(3).SetCellValue("有效发言次数");
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].qq);
rowtemp.CreateCell(1).SetCellValue(list[i].nickname);
rowtemp.CreateCell(2).SetCellValue(list[i].prize);
rowtemp.CreateCell(3).SetCellValue(list[i].speakNum);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "获奖表.xls"));
}
public ActionResult ExportClientCouponCode(int parentId, int isBind, string channel)
{
CompanyClientManager manager = new CompanyClientManager();
var result = manager.SelectCouponCodeByParentId(parentId, isBind);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
var fileName = channel + DateTime.Now.ToString("yyyy_MM_dd_HHmm") + ".xls";
row1.CreateCell(0).SetCellValue("渠道");
row1.CreateCell(1).SetCellValue("活动券码");
row1.CreateCell(2).SetCellValue("手机号");
row1.CreateCell(3).SetCellValue("创建时间");
if (result != null && result.Any())
{
for (var i = 0; i < result.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(channel);
rowtemp.CreateCell(1).SetCellValue(result[i].CouponCode);
rowtemp.CreateCell(2).SetCellValue(result[i].Telephone);
rowtemp.CreateCell(3).SetCellValue(result[i].CreatedTime.ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", fileName));
}
private void btnOrderReport2_Click(object sender, EventArgs e)
{
if (dGV_reportAll.Rows.Count == 0)
{
MessageBox.Show("当前无数据导出！请先刷新今日订货信息，确认当前有无当日订货信息。");
}
else
{
string Date = DateTime.Now.ToString("yyyy-MM-dd");
SaveFileDialog s1 = new SaveFileDialog();
s1.Title = "请选择要导出的位置";
s1.Filter = "Excel文件|*.xls";
s1.FileName = dTP_oStartTime.Value.ToShortDateString().Replace('/', '-') + "至" + dTP_oEndTime.Value.ToShortDateString().Replace('/', '-') + "历史订货信息";
if (s1.ShowDialog() == DialogResult.OK)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(Date);
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("订货序号");
row1.CreateCell(1).SetCellValue("零件编号");
row1.CreateCell(2).SetCellValue("供应商编号");
row1.CreateCell(3).SetCellValue("订货数量");
row1.CreateCell(4).SetCellValue("订货时间");
for (int i = 0; i < dGV_reportAll.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);
rowTemp.CreateCell(0).SetCellValue(i + 1);
rowTemp.CreateCell(1).SetCellValue(dGV_reportAll.Rows[i].Cells[0].Value.ToString());
rowTemp.CreateCell(2).SetCellValue(dGV_reportAll.Rows[i].Cells[1].Value.ToString());
rowTemp.CreateCell(3).SetCellValue(dGV_reportAll.Rows[i].Cells[2].Value.ToString());
rowTemp.CreateCell(4).SetCellValue(dGV_reportAll.Rows[i].Cells[3].Value.ToString());
}
FileStream orderRepo = File.OpenWrite(s1.FileName.ToString());
try
{
book.Write(orderRepo);
orderRepo.Seek(0, SeekOrigin.Begin);
MessageBox.Show("导出成功！", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception)
{
MessageBox.Show("导出失败！", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw;
}
finally
{
if (orderRepo != null)
{
orderRepo.Close();
}
}
}
}
}
public void DataExportToExecl()
{
OrganizeInfoDal dal = new OrganizeInfoDal();
DbSession dbSession = new DbSession();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
var _data = dal.GetEntities(u => true).ToList();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("排名");
row1.CreateCell(1).SetCellValue("CardID");
row1.CreateCell(2).SetCellValue("姓名");
row1.CreateCell(3).SetCellValue("手机");
row1.CreateCell(4).SetCellValue("职位");
row1.CreateCell(5).SetCellValue("所在公司");
row1.CreateCell(6).SetCellValue("创建时间");
//将数据逐步写入sheet1各个行
for (int i = 0; i < _data.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(i + 1);
rowtemp.CreateCell(1).SetCellValue(_data[i].OrganizeInfoShowName);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
var buf = ms.ToArray();
using (FileStream fs = new FileStream("test.xls", FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
public FileResult Excel2()
{
MyElectrCheck_DBDataContext mdc = new MyElectrCheck_DBDataContext();
List <DataEliminate> list = new List <DataEliminate>();
if (Session["solvelist"] != null)
{
//将session转为集合
list = (List <DataEliminate>)Session["solvelist"];
}
NPOI.HSSF.UserModel.HSSFWorkbook Book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = Book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("任务编号");
row1.CreateCell(1).SetCellValue("任务名称");
row1.CreateCell(2).SetCellValue("线路编号");
row1.CreateCell(3).SetCellValue("线路编号");
row1.CreateCell(4).SetCellValue("有无故障");
row1.CreateCell(5).SetCellValue("缺陷级别");
row1.CreateCell(6).SetCellValue("缺陷类型");
row1.CreateCell(7).SetCellValue("消缺时间");
row1.CreateCell(8).SetCellValue("发现时间");
row1.CreateCell(9).SetCellValue("缺陷描述");
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].solveTaskCode);
rowtemp.CreateCell(1).SetCellValue(list[i].solveTaskName);
rowtemp.CreateCell(2).SetCellValue(list[i].lineCode);
if (list[i].isBug == 1)
{
rowtemp.CreateCell(3).SetCellValue("有");
}
else
{
rowtemp.CreateCell(3).SetCellValue("无");
}
rowtemp.CreateCell(4).SetCellValue(list[i].bugLevelName);
rowtemp.CreateCell(5).SetCellValue(list[i].bugLevelName);
rowtemp.CreateCell(6).SetCellValue(list[i].bugTypeName);
rowtemp.CreateCell(7).SetCellValue(list[i].finishTime.ToString());
rowtemp.CreateCell(8).SetCellValue(list[i].discoverTime.ToString());
rowtemp.CreateCell(9).SetCellValue(list[i].bugDesc);
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
Book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
DateTime dt = DateTime.Now;
string dateTime = dt.ToString("yyMMddHHmmssff");
string fileName = "查询结果" + dateTime + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public string ExportCusConfig()
{
JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name);
string sql = QueryCondition_customsconfig();
sql += " order by a.entrusttypecode,a.busiitemcode asc";
IsoDateTimeConverter iso = new IsoDateTimeConverter();//序列化JSON对象时,日期的处理格式
iso.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
DataTable dt_count = DBMgr.GetDataTable("select count(1) from (" + sql + ") a");
int WebDownCount = Convert.ToInt32(ConfigurationManager.AppSettings["WebDownCount"]);
if (Convert.ToInt32(dt_count.Rows[0][0]) > WebDownCount)
{
return("{success:false,WebDownCount:" + WebDownCount + "}");
}
DataTable dt = DBMgr.GetDataTable(sql);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个导出成功sheet
NPOI.SS.UserModel.ISheet sheet_S;
string filename = "业务细项.xls";
sheet_S = book.CreateSheet("业务细项");
NPOI.SS.UserModel.IRow row1 = sheet_S.CreateRow(0);
row1.CreateCell(0).SetCellValue("业务类别编号"); row1.CreateCell(1).SetCellValue("业务类别名称"); row1.CreateCell(2).SetCellValue("业务细项编号"); row1.CreateCell(3).SetCellValue("业务细项名称");
row1.CreateCell(4).SetCellValue("创建时间"); row1.CreateCell(5).SetCellValue("是否启用"); row1.CreateCell(6).SetCellValue("创建人"); row1.CreateCell(7).SetCellValue("启禁用人");
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet_S.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["ENTRUSTTYPECODE"].ToString());
rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["ENTRUSTTYPENAME"].ToString());
rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["BUSIITEMCODE"].ToString());
rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["BUSIITEMNAME"].ToString());
rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["STARTTIME"].ToString());
rowtemp.CreateCell(5).SetCellValue(dt.Rows[i]["ENABLE"].ToString() == "1" ? "是" : "否");
rowtemp.CreateCell(6).SetCellValue(dt.Rows[i]["CREATEUSERNAME"].ToString());
rowtemp.CreateCell(7).SetCellValue(dt.Rows[i]["ENABLEUSERNAME"].ToString());
}
// 写入到客户端
//System.IO.MemoryStream ms = new System.IO.MemoryStream();
//book.Write(ms);
//ms.Seek(0, SeekOrigin.Begin);
//return File(ms, "application/vnd.ms-excel", filename);
return(Extension.getPathname(filename, book));
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="response"></param>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="sheetname"></param>
public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.IFont font = book.CreateFont();
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "微软雅黑";
style.SetFont(font);
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls"));
response.ContentType = "application/vnd.ms-excel";
//response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
private static int createCell(NPOI.SS.UserModel.IRow row, int col, float?nullableFloat)
{
if (nullableFloat.HasValue)
{
row.CreateCell(col++).SetCellValue(nullableFloat.Value);
}
else
{
row.CreateCell(col++).SetCellValue("");
}
return(col);
}
public FileResult ExportDetail(string storeId, string StartDate, string EndDate, string storeType)
{
using (DBContext db = new DBContext())
{
var orderQuery = db.Order.AsQueryable();
orderQuery = SetQuery(orderQuery, storeId, StartDate, EndDate);
var storeQuery = db.Store.AsQueryable();
if (!string.IsNullOrEmpty(storeType))
{
StoreType type = (StoreType)Convert.ToInt16(storeType); storeQuery = storeQuery.Where(q => q.StoreType == type);
}
var list = (from q in db.OrderItem
join o in orderQuery on q.OrderId equals o.ID
join t in storeQuery on o.StoreId equals t.ID
group q by new { o.StoreId, o.StoreName, q.ProductName, q.ProductCode } into s
orderby new { s.Key.StoreName, s.Key.ProductName }
select new PSMX()
{
StoreName = s.Key.StoreName, ProductName = s.Key.ProductName, ProductCode = s.Key.ProductCode, ProductNumber = s.Sum(p => p.RealNumber)
}).ToList();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("分店");
row1.CreateCell(1).SetCellValue("商品名称");
row1.CreateCell(2).SetCellValue("商品编号");
row1.CreateCell(3).SetCellValue("采购总数");
string status = string.Empty;
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].StoreName);
rowtemp.CreateCell(1).SetCellValue(list[i].ProductName);
rowtemp.CreateCell(2).SetCellValue(list[i].ProductCode);
rowtemp.CreateCell(3).SetCellValue(list[i].ProductNumber);
}
DateTime now = DateTime.Now;
// 写入到客户端
return(ExportExcel(book, now.ToString("yyMMddHHmmssfff")));
}
}
public string ExportFileRecoginze()
{
JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name);
string sql = QueryConditionFileRecoginze();
sql = sql + " order by lf.TIMES desc";
IsoDateTimeConverter iso = new IsoDateTimeConverter();//序列化JSON对象时,日期的处理格式
iso.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
DataTable dt_count = DBMgr.GetDataTable("select count(1) from (" + sql + ") a");
int WebDownCount = Convert.ToInt32(ConfigurationManager.AppSettings["WebDownCount"]);
if (Convert.ToInt32(dt_count.Rows[0][0]) > WebDownCount)
{
return("{success:false,WebDownCount:" + WebDownCount + "}");
}
DataTable dt = DBMgr.GetDataTable(sql);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个导出成功sheet
NPOI.SS.UserModel.ISheet sheet_S;
string filename = "文件关联.xls";
sheet_S = book.CreateSheet("文件关联");
NPOI.SS.UserModel.IRow row1 = sheet_S.CreateRow(0);
row1.CreateCell(0).SetCellValue("关联状态"); row1.CreateCell(1).SetCellValue("订单编号"); row1.CreateCell(2).SetCellValue("客户编号");
row1.CreateCell(3).SetCellValue("创建时间"); row1.CreateCell(4).SetCellValue("创建人");
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet_S.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["STATUS"].ToString());
rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["ORDERCODE"].ToString());
rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["CUSNO"].ToString());
rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["TIMES"].ToString());
rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["USERNAME"].ToString());
}
// 写入到客户端
//System.IO.MemoryStream ms = new System.IO.MemoryStream();
//book.Write(ms);
//ms.Seek(0, SeekOrigin.Begin);
//return File(ms, "application/vnd.ms-excel", filename);
return(Extension.getPathname(filename, book));
}
private void exportData()
{
string sql = @"SELECT * FROM cusdoc.sys_customer order by id desc";
DataTable dt = DBMgr.GetDataTable(sql);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个导出成功sheet
NPOI.SS.UserModel.ISheet sheet_S = book.CreateSheet("客商信息");
NPOI.SS.UserModel.IRow row1 = sheet_S.CreateRow(0);
row1.CreateCell(0).SetCellValue("代码");
row1.CreateCell(1).SetCellValue("海关代码");
row1.CreateCell(2).SetCellValue("国检代码");
row1.CreateCell(3).SetCellValue("中文名称");
row1.CreateCell(4).SetCellValue("中文简称");
row1.CreateCell(5).SetCellValue("中文地址");
row1.CreateCell(6).SetCellValue("英文名称");
row1.CreateCell(7).SetCellValue("英文地址");
row1.CreateCell(8).SetCellValue("是否启用");
row1.CreateCell(9).SetCellValue("备注");
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet_S.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["CODE"].ToString());
rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["HSCODE"].ToString());
rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["CIQCODE"].ToString());
rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["NAME"].ToString());
rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["CHINESEABBREVIATION"].ToString());
rowtemp.CreateCell(5).SetCellValue(dt.Rows[i]["CHINESEADDRESS"].ToString());
rowtemp.CreateCell(6).SetCellValue(dt.Rows[i]["ENGLISHNAME"].ToString());
rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["ENGLISHADDRESS"].ToString());
rowtemp.CreateCell(8).SetCellValue(dt.Rows[i]["ENABLED"].ToString() == "1" ? "是" : "否");
rowtemp.CreateCell(9).SetCellValue(dt.Rows[i]["REMARK"].ToString());
}
try
{
// 输出Excel
string filename = "客商信息.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", Server.UrlEncode(filename)));
Response.Clear();
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.BinaryWrite(ms.GetBuffer());
Response.End();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public static MemoryStream ExportAuditLog(int zoneTableId, DateTime?startTime, DateTime?endTime, string logType, string detail)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//List<AuditLogEntity> list = new List<AuditLogEntity>();
//for (int i = 0; i < 50; i++)
//{
// AuditLogEntity en = new AuditLogEntity();
// en.ID = i;
// en.LogType = "App";
// en.Detail = "detail" + i;
// en.LogTime = DateTime.Now;
// en.LogOperator = "Michael.he";
// list.Add(en);
//}
List <AuditLogEntity> list = AuditLogAccess.GetList(zoneTableId, startTime, endTime, logType, detail);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("Log Type");
row1.CreateCell(1).SetCellValue("Detail");
row1.CreateCell(2).SetCellValue("Log Time");
row1.CreateCell(3).SetCellValue("Log Operator");
var notesStyle = book.CreateCellStyle();
notesStyle.WrapText = true;//设置换行这个要先设置
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
var rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].LogType.ToString());
rowtemp.CreateCell(1).SetCellValue(list[i].Detail.Replace("<br />", "\n"));
rowtemp.Cells[1].CellStyle = notesStyle;
rowtemp.CreateCell(2).SetCellValue(list[i].LogTime.ToString(CultureInfo.InvariantCulture));
rowtemp.CreateCell(3).SetCellValue(list[i].LogOperator);
}
sheet1.SetColumnWidth(1, 160 * 256);
sheet1.SetColumnWidth(2, 20 * 256);
// 写入到客户端
var ms = new MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
public static void export(List <Comment> comments)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("users");
// 第一列
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
string[] headers = new string[] {
"昵称", "邮箱", "评价星级", "排名", "个人主页"
};
for (int i = 0; i < headers.Length; i++)
{
row.CreateCell(i).SetCellValue(headers[i]);
}
for (int i = 0; i < comments.Count; i++)
{
Comment c = comments[i];
// 第二列
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
row2.CreateCell(0).SetCellValue(c.NickName);
row2.CreateCell(1).SetCellValue(c.Email);
row2.CreateCell(2).SetCellValue(c.Star);
row2.CreateCell(3).SetCellValue(c.Rank);
row2.CreateCell(4).SetCellValue(c.Profile);
}
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel文件(*.xls)|*.xls";
if (dialog.ShowDialog() == DialogResult.OK)
{
string fileName = dialog.FileName;
try
{
FileStream stream = new FileStream(fileName, FileMode.Create);
using (stream)
{
book.Write(stream);
}
}
catch (Exception e) {
MessageBox.Show("保存文件失败！");
}
}
}
/// <summary>
/// 设置样式
/// </summary>
private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.ICell newCell = null;
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
else if (strColDataType.Equals("System.DateTime"))
{
DateTime dateV;
DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV);
newCell = row2.CreateCell(j);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
newCell.CellStyle = cellStyle;
}
}
}
}
public FileResult Export(int id)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //创建Excel文件的对象
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); //给sheet1添加第一行的头部标题
row1.CreateCell(0).SetCellValue("帐号");
row1.CreateCell(1).SetCellValue("姓名");
row1.CreateCell(2).SetCellValue("过期时间");
row1.CreateCell(3).SetCellValue("状态");
row1.CreateCell(4).SetCellValue("角色");
bool enable = id == 1 ? true : false;
List <VModel.SyUserManager.Grid> list = Bll.SyUserBll.ExportManager(enable);
int i = 1;
foreach (var m in list)
{
NPOI.SS.UserModel.IRow r = sheet1.CreateRow(i);
r.CreateCell(0).SetCellValue(m.Account);
r.CreateCell(1).SetCellValue(m.Name);
r.CreateCell(2).SetCellValue(Common.Function.ConvertDate(m.ExpiresTime));
r.CreateCell(3).SetCellValue(Common.Dict.UserState.GetVal(m.IsEnabled));
if (m.RoleNames != null && m.RoleNames.Count > 0)
{
string roleName = "";
foreach (var name in m.RoleNames)
{
roleName += name + "，";
}
if (roleName != "")
{
roleName = roleName.Substring(0, roleName.Length - 1);
}
r.CreateCell(4).SetCellValue(roleName);
}
else
{
r.CreateCell(4).SetCellValue("");
}
i++;
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();// 写入到客户端
book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
string fileName = enable == true ? "当前管理员用户.xls" : "历史管理员用户.xls";
return(File(ms, "application/vnd.ms-excel", UrlEcode.ToHexString(fileName)));
}
/// <summary>
/// NPOI方式导出excel
/// 直接导出
/// </summary>
/// <returns></returns>
public FileResult ExportStu2()
{
string schoolname = "401";
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//假数据(真实数据需要从数据库中获取)
List <Compture> listRainInfo = new List <Compture>()
{
new Compture()
{
PCName = "pc1", UserName = "******"
},
new Compture()
{
PCName = "pc2", UserName = "******"
},
new Compture()
{
PCName = "pc3", UserName = "******"
},
new Compture()
{
PCName = "pc4", UserName = "******"
},
};
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("电脑号");
row1.CreateCell(1).SetCellValue("姓名");
//将数据逐步写入sheet1各个行
for (int i = 0; i < listRainInfo.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].PCName.ToString());
rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].UserName.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "第一批电脑派位生名册.xls"));
}
public FileResult ExportNumber(string ProductName, string StartDate, string EndDate)
{
using (DBContext db = new DBContext())
{
var itemQuery = db.OrderItem.AsQueryable();
var orderQuery = db.Order.AsQueryable();
orderQuery = GetOrderQuery(orderQuery, StartDate, EndDate);
if (!string.IsNullOrEmpty(ProductName))
{
itemQuery = itemQuery.Where(q => q.ProductName.Equals(ProductName));
}
var list = (from q in itemQuery
join o in orderQuery on q.OrderId equals o.ID
group q by new { q.ProductName, q.ProductCode } into s
select new CXSL()
{
ProductName = s.Key.ProductName, ProductCode = s.Key.ProductCode, ProductNumber = s.Sum(p => p.RealNumber)
}).OrderByDescending(q => q.ProductNumber).ToList();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("商品名称");
row1.CreateCell(1).SetCellValue("商品编号");
row1.CreateCell(2).SetCellValue("数量");
string status = string.Empty;
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].ProductName);
rowtemp.CreateCell(1).SetCellValue(list[i].ProductCode);
rowtemp.CreateCell(2).SetCellValue(list[i].ProductNumber);
}
DateTime now = DateTime.Now;
// 写入到客户端
return(ExportExcel(book, now.ToString("yyMMddHHmmssfff")));
}
}
public Stream ExportToExecl()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
var orgData = CurrentDal.GetEntities(u => u.OrganizeInfoManageId != null).AsQueryable(); //获取list数据，也可以分页获取数据，以获得更高效的性能
var _data = orgData.ToList();
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("志愿者团队ID");
row1.CreateCell(1).SetCellValue("团队登录名");
row1.CreateCell(2).SetCellValue("团队名称");
row1.CreateCell(3).SetCellValue("负责人");
row1.CreateCell(4).SetCellValue("联系方式");
row1.CreateCell(5).SetCellValue("邮箱");
row1.CreateCell(6).SetCellValue("活动数目");
row1.CreateCell(7).SetCellValue("创建时间");
row1.CreateCell(8).SetCellValue("最后登录时间");
row1.CreateCell(9).SetCellValue("团队状态");
//将数据逐步写入sheet1各个行
for (int i = 0; i < _data.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(_data[i].OrganizeInfoID);
rowtemp.CreateCell(1).SetCellValue(_data[i].OrganizeInfoLoginId);
rowtemp.CreateCell(2).SetCellValue(_data[i].OrganizeInfoShowName);
rowtemp.CreateCell(3).SetCellValue(_data[i].OrganizeInfoPeople);
rowtemp.CreateCell(4).SetCellValue(_data[i].OrganizeInfoPhone);
rowtemp.CreateCell(5).SetCellValue(string.IsNullOrEmpty(_data[i].OrganizeInfoEmail) ? "无" : _data[i].OrganizeInfoEmail);
rowtemp.CreateCell(6).SetCellValue(_data[i].ActivityCount.ToString());
rowtemp.CreateCell(7).SetCellValue(_data[i].CreateTime.ToString());
rowtemp.CreateCell(8).SetCellValue(_data[i].OrganizeInfoLastTime.ToString());
rowtemp.CreateCell(9).SetCellValue(_data[i].Status == 0 ? "正常" : _data[i].Status == 1 ? "删除" : _data[i].Status == 2 ?"审核中": "无效");
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
public FileResult ExportExcel(string dateBegin, string dateEnd)
{
string Condition = " and 1=1";
if (!string.IsNullOrEmpty(dateBegin))
{
Condition += " and PatiInBillItem.CreateDate >= '" + DateTime.Parse(dateBegin).ToString("yyyy-MM-dd 00:00:00") + "'";
}
if (!string.IsNullOrEmpty(dateEnd))
{
Condition += " and PatiInBillItem.CreateDate <= '" + DateTime.Parse(dateEnd).ToString("yyyy-MM-dd 23:59:59") + "'";
}
IList <UserBillTotal> billTotal = new List <UserBillTotal>();
DataTable lstBillItem = billitemLogic.GetPersonBillTypeAmount(Condition, 9999, 1, "", "");
if (lstBillItem != null && lstBillItem.Rows.Count > 0)
{
foreach (DataRow drNew in lstBillItem.Rows)
{
UserBillTotal item = new UserBillTotal();
item.BillDate = drNew[0].ToString();
item.TotalAmount = decimal.Parse(drNew[1].ToString());
billTotal.Add(item);
}
}
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
// List<modelList> listRainInfo = m_BLL.GetSchoolListAATQ(schoolname);
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("就餐日期");
row1.CreateCell(1).SetCellValue("金额");
//将数据逐步写入sheet1各个行
for (int i = 0; i < billTotal.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(billTotal[i].BillDate.ToString());
rowtemp.CreateCell(1).SetCellValue(billTotal[i].TotalAmount.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));
}
