private void FillObject2Row(object obj, NPOI.SS.UserModel.IRow row, NPOI.SS.UserModel.IRow header, System.Type type)
{
var props = type.GetProperties();
foreach (var j in props)
{
var mtAttr = j.GetCustomAttributes(typeof(Rtti.MetaDataAttribute), true);
if (mtAttr == null || mtAttr.Length == 0)
{
continue;
}
var value = j.GetValue(obj, null);
var cell = GetCellSure(row, header, j.Name, j.PropertyType);
if (cell == null)
{
EngineNS.Profiler.Log.WriteLine(EngineNS.Profiler.ELogTag.Warning, "Excel", $"Column {j.Name} is not found");
continue;
}
if (value == null)
{
EngineNS.Profiler.Log.WriteLine(EngineNS.Profiler.ELogTag.Warning, "Excel Value", $"Name {j.Name} is null");
continue;
}
Object2Cell(j, value, cell);
}
}
public static void ExportExcel(DataTable dt, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && dt != null && dt.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
//循环读取DataTable列名称
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).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++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
//写到客户端
using (MemoryStream ms = new MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
}
protected NPOI.SS.UserModel.ICell GetCellSure(NPOI.SS.UserModel.IRow row, NPOI.SS.UserModel.IRow header, string name, System.Type type)
{
int index = -1;
for (int i = 1; i < header.LastCellNum; i++)
{
var headCell = header.GetCell(i);
//if (headCell == null)
// continue;
if (headCell.StringCellValue == name)
{
index = i;
break;
}
}
if (index == -1)
{
return(null);
}
var cell = row.GetCell(index);
if (cell == null)
{
cell = row.CreateCell(index, Type2CellType(type));
}
return(cell);
}
/// <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 ExportData(string keyword)
{
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("帳戶號碼");
var i = 0;
var data = bankRepo.Where(keyword).ToList();
foreach (var item in data)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(item.客戶資料.客戶名稱);
rowtemp.CreateCell(1).SetCellValue(item.銀行名稱);
rowtemp.CreateCell(2).SetCellValue(item.銀行代碼);
rowtemp.CreateCell(3).SetCellValue(Convert.ToString(item.分行代碼));
rowtemp.CreateCell(4).SetCellValue(item.帳戶名稱);
rowtemp.CreateCell(5).SetCellValue(Convert.ToString(item.帳戶號碼));
i++;
}
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 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 Export(string OrderCode, string StoreId, string Tel, string StartDate, string EndDate, string Status)
{
using (DBContext db = new DBContext())
{
var query = db.Order.AsQueryable();
Store store = UserContext.store;
StoreId = store == null ? StoreId : store.ID;
query = SetQuery(query, OrderCode, StoreId, Tel, StartDate, EndDate, Status);
//获取list数据
var list = query.OrderByDescending(q => q.SubmitTime).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("联系电话");
row1.CreateCell(4).SetCellValue("下单时间");
row1.CreateCell(5).SetCellValue("订单金额");
row1.CreateCell(6).SetCellValue("订单状态");
string status = string.Empty;
//将数据逐步写入sheet1各个行
for (int i = 0; i < list.Count; i++)
{
switch (list[i].Status)
{
case OrderStatus.BeforeSend: status = "待发货"; break;
case OrderStatus.BeforeSubmit: status = "待提交"; break;
case OrderStatus.Reject: status = "驳回"; break;
case OrderStatus.Sended: status = "已发货"; break;
}
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].OrderCode);
rowtemp.CreateCell(1).SetCellValue(list[i].StoreName);
rowtemp.CreateCell(2).SetCellValue(list[i].Creator);
rowtemp.CreateCell(3).SetCellValue(list[i].Tel);
rowtemp.CreateCell(4).SetCellValue(list[i].SubmitTime.ToString());
rowtemp.CreateCell(5).SetCellValue(list[i].Paid.ToString());
rowtemp.CreateCell(6).SetCellValue(status);
}
DateTime now = DateTime.Now;
// 写入到客户端
return(ExportExcel(book, now.ToString("yyMMddHHmmssfff")));
}
}
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));
}
public FileResult ExportBanks()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Banks");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
var contactors = repo.All().ToList();
row1.CreateCell(0).SetCellValue("銀行名稱");
row1.CreateCell(1).SetCellValue("銀行代碼");
row1.CreateCell(2).SetCellValue("分行代碼");
row1.CreateCell(3).SetCellValue("帳戶名稱");
row1.CreateCell(4).SetCellValue("帳戶號碼");
row1.CreateCell(5).SetCellValue("客戶名稱");
for (int i = 0; i < contactors.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(contactors[i].銀行名稱.ToString());
rowtemp.CreateCell(1).SetCellValue(contactors[i].銀行代碼.ToString());
rowtemp.CreateCell(2).SetCellValue(contactors[i].分行代碼.ToString());
rowtemp.CreateCell(3).SetCellValue(contactors[i].帳戶名稱.ToString());
rowtemp.CreateCell(4).SetCellValue(contactors[i].帳戶號碼.ToString());
rowtemp.CreateCell(5).SetCellValue(contactors[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", "Banks.xls"));
}
public FileResult ExportContacotrs()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Contactors");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
var contactors = repo.All().ToList();
row1.CreateCell(0).SetCellValue("職稱");
row1.CreateCell(1).SetCellValue("姓名");
row1.CreateCell(2).SetCellValue("電子郵件");
row1.CreateCell(3).SetCellValue("手機");
row1.CreateCell(4).SetCellValue("電話");
row1.CreateCell(5).SetCellValue("客戶名稱");
for (int i = 0; i < contactors.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(contactors[i].職稱.ToString());
rowtemp.CreateCell(1).SetCellValue(contactors[i].姓名.ToString());
rowtemp.CreateCell(2).SetCellValue(contactors[i].Email.ToString());
rowtemp.CreateCell(3).SetCellValue(contactors[i].手機.ToString());
rowtemp.CreateCell(4).SetCellValue(contactors[i].電話.ToString());
rowtemp.CreateCell(5).SetCellValue(contactors[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", "Contactors.xls"));
}
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 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.AppOrder.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.CreateTime.CompareTo(start) > 0 && q.CreateTime.CompareTo(end) < 0 && q.Status == 5);
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.Payable } by new { q.StoreName } into s
select new PSJE()
{
StoreName = s.Key.StoreName, Pay = s.Sum(p => p.Payable == null ? 0 : p.Payable)
}).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>
/// 获取不动产的办件步骤信息
/// </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 WriteExcel(DataTable dt, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && dt != null && dt.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).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++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
MessageBox.Show("導出成功：" + filePath, "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void SetHeaderOptions(SimpleExcelExport.Column column, NPOI.SS.UserModel.IRow row, int columnNumber, ExportToExcel exportToExcel)
{
HSSFCellStyle style = (HSSFCellStyle)document.CreateCellStyle();
var font = document.CreateFont();
if (column.HFontBold)
{
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
}
if (!string.IsNullOrEmpty(column.HFontColor))
{
System.Drawing.Color fontColor = exportToExcel.GetColor(column.ColumnName, column.HFontColor, typeof(string));
if (!fontColor.IsEmpty)
{
font.Color = GetXLColour(fontColor);
}
}
if (!string.IsNullOrEmpty(column.HBackColor))
{
System.Drawing.Color backgroundColor = exportToExcel.GetColor(column.ColumnName, column.HBackColor, typeof(string));
if (!backgroundColor.IsEmpty)
{
style.FillForegroundColor = GetXLColour(backgroundColor);
style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
}
}
style.SetFont(font);
row.Cells[columnNumber].CellStyle = style;
}
public FileResult ExportExcelForCustomerData(List <CustomerViewRes> 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("客户类型");
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("详细地址");
row1.CreateCell(10).SetCellValue("自定义编号1");
row1.CreateCell(11).SetCellValue("自定义编号2");
row1.CreateCell(12).SetCellValue("自定义编号3");
row1.CreateCell(13).SetCellValue("备注");
row1.CreateCell(14).SetCellValue("建档时间");
row1.CreateCell(15).SetCellValue("最后编辑时间");
row1.CreateCell(16).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].customerNo);
rowtemp.CreateCell(1).SetCellValue(list[i].customerName);
rowtemp.CreateCell(2).SetCellValue(list[i].customerType);
rowtemp.CreateCell(3).SetCellValue(list[i].useStateName);
rowtemp.CreateCell(4).SetCellValue(list[i].factoryName);
rowtemp.CreateCell(5).SetCellValue(list[i].meterTypeName);
rowtemp.CreateCell(6).SetCellValue(list[i].telNo);
rowtemp.CreateCell(7).SetCellValue(list[i].mobileNo);
rowtemp.CreateCell(8).SetCellValue(list[i].estateName);
rowtemp.CreateCell(9).SetCellValue(list[i].address);
rowtemp.CreateCell(10).SetCellValue(list[i].defineNo1);
rowtemp.CreateCell(11).SetCellValue(list[i].defineNo2);
rowtemp.CreateCell(12).SetCellValue(list[i].defineNo3);
rowtemp.CreateCell(13).SetCellValue(list[i].remark);
rowtemp.CreateCell(14).SetCellValue(list[i].buildTime);
rowtemp.CreateCell(15).SetCellValue(list[i].editTime);
rowtemp.CreateCell(16).SetCellValue(list[i].Operator);
}
System.IO.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 ExportExcelForMeterData(List <OneFLMeterDataViewRes> 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("通讯编号");
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("温度");
row1.CreateCell(10).SetCellValue("压力");
row1.CreateCell(11).SetCellValue("剩余金额");
row1.CreateCell(12).SetCellValue("剩余气量");
row1.CreateCell(13).SetCellValue("供电电压");
row1.CreateCell(14).SetCellValue("表具状态");
row1.CreateCell(15).SetCellValue("阀门状态");
row1.CreateCell(16).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].LoginStateMsg);
rowtemp.CreateCell(1).SetCellValue(list[i].deviceNo);
rowtemp.CreateCell(2).SetCellValue(list[i].communicateNo);
rowtemp.CreateCell(3).SetCellValue(list[i].customerName);
rowtemp.CreateCell(4).SetCellValue(list[i].address);
rowtemp.CreateCell(5).SetCellValue(list[i].StdSum);
rowtemp.CreateCell(6).SetCellValue(list[i].WorkSum);
rowtemp.CreateCell(7).SetCellValue(list[i].StdFlow);
rowtemp.CreateCell(8).SetCellValue(list[i].WorkFlow);
rowtemp.CreateCell(9).SetCellValue(list[i].Temperature);
rowtemp.CreateCell(10).SetCellValue(list[i].Pressure);
rowtemp.CreateCell(11).SetCellValue(list[i].RemainMoney);
rowtemp.CreateCell(12).SetCellValue(list[i].RemainVolume);
rowtemp.CreateCell(13).SetCellValue(list[i].PowerVoltage);
rowtemp.CreateCell(14).SetCellValue(list[i].FMStateMsg);
rowtemp.CreateCell(15).SetCellValue(list[i].ValveStateMsg);
rowtemp.CreateCell(16).SetCellValue(list[i].InstantTime);
}
System.IO.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 static Row GetAdapter(this NpoiRow row)
{
if (null == row)
{
return(null);
}
return(new Row(row));
}
//将datatable导出到excel
public static ReturnMsg ExportList(ref MemoryStream ms, IList list, int type = 0, string path = "")
{
DataTable dt;
try
{
dt = ToDataTable(list);//将数据源转换为datatable类型
//创建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);
int i = 0;
foreach (DataColumn column in dt.Columns)
{
row1.CreateCell(i).SetCellValue(column.ColumnName);
i++;
}
int j = 0;
foreach (DataRow dr in dt.Rows)
{
i = 0;
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(j + 1);
for (; i < dt.Columns.Count; i++)
{
rowtemp.CreateCell(i).SetCellValue(dr[i].ToString());
}
j++;
}
if (type == 0)
{
MemoryStream ms1 = new MemoryStream();
book.Write(ms1);
ms1.Seek(0, SeekOrigin.Begin);
ms = ms1;
}
else
{
FileStream fs = new FileStream(path, FileMode.Create);
book.Write(fs);
fs.Close();
ms = null;
}
return(new ReturnMsg()
{
code = 0, msg = ""
});
}
catch (Exception ex)
{
ms = null;
return(new ReturnMsg()
{
code = 1, msg = "导出异常：" + ex.Message
});
}
}
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();
}
}
}
}
}
//To Delete
//public ActionResult Importool()
//{
// var result = new ResultInfoModel()
// {
// IsSuccess = false
// };
// StringBuilder strbuild = new StringBuilder();
// string FileName;
// string savePath;
// HttpPostedFileBase file = Request.Files["file"];
// if (file == null || file.ContentLength <= 0)
// {
// result.Message = "please choose file";
// return Content(JsonHelper.JsonSerializer(result));
// }
// else
// {
// string fileName = Path.GetFileName(file.FileName);
// int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
// string fileEx = Path.GetExtension(fileName);//获取上传文件的扩展名
// string NoFileName = Path.GetFileNameWithoutExtension(fileName);//获取无扩展名的文件名
// int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
// string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
// FileName = NoFileName + fileEx;
// if (!FileType.Contains(fileEx))
// {
// result.Message = "please upload .xls and .xlsx";
// return Content(JsonHelper.JsonSerializer(result));
// }
// if (filesize >= Maxsize)
// {
// result.Message = string.Format("file size can't big than {0}", Maxsize);
// return Content(JsonHelper.JsonSerializer(result));
// }
// string path = Server.MapPath("~/App_Data/uploads");
// savePath = Path.Combine(path, FileName);
// file.SaveAs(savePath);
// }
// string strConn;
// strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties=Excel 12.0;";
// using (OleDbConnection conn = new OleDbConnection(strConn))
// {
// conn.Open();
// OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
// DataSet myDataSet = new DataSet();
// try
// {
// myCommand.Fill(myDataSet, "ExcelInfo");
// }
// catch (Exception ex)
// {
// result.Message = ex.Message;
// return Content(JsonHelper.JsonSerializer(result));
// }
// DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
// var importResult = new Importresult();
// importResult.FalseInfo = new List<FalseInfo>();
// try
// {
// for (int i = 0; i < table.Rows.Count; i++)
// {
// var model = new MaToolModel
// {
// //BMCode = table.Rows[i]["Code"].ToString() ?? "",
// //BMCodeDesc = table.Rows[i]["CodeDesc"].ToString() ?? "",
// //BMType = MTTypeEnum.Tool.GetHashCode(),
// //BMIsValid = EnabledEnum.Enabled.GetHashCode(),
// };
// var inserResult = MaterialBusiness.SaveMaTool(model, LoginUser);
// }
// result.IsSuccess = true;
// }
// catch (Exception ex)
// {
// result.Message = ex.Message;
// return Content(JsonHelper.JsonSerializer(result));
// }
// conn.Close();
// }
// return Content(JsonHelper.JsonSerializer(result));
//}
#endregion
#region 导出
public FileResult MaterialExcel(MaterialSearchModel searchModel)
{
searchModel.PageSize = 1000;
var totalCount = 0;
var result = MaterialBusiness.SearchMaterialPageList(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("Process Type");
row1.CreateCell(1).SetCellValue("Customer");
row1.CreateCell(2).SetCellValue("RASP P/N");
row1.CreateCell(3).SetCellValue("Part Name");
row1.CreateCell(4).SetCellValue("Production Unit");
row1.CreateCell(5).SetCellValue("Customer P/N ");
row1.CreateCell(6).SetCellValue("Cavity");
row1.CreateCell(7).SetCellValue("Cycletime");
row1.CreateCell(8).SetCellValue("cycletime/cav");
row1.CreateCell(9).SetCellValue("Standard Headcount");
row1.CreateCell(10).SetCellValue("Standard scrap");
row1.CreateCell(11).SetCellValue("Materials P/N");
row1.CreateCell(12).SetCellValue("g/cav(SAP)");
row1.CreateCell(13).SetCellValue("Mold No.");
row1.CreateCell(14).SetCellValue("ASS AC");
row1.CreateCell(15).SetCellValue("Work Order");
for (int i = 0; i < result.Count(); i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(result[i].MIProcessType);
rowtemp.CreateCell(1).SetCellValue(result[i].MICustomer);
rowtemp.CreateCell(2).SetCellValue(result[i].MISapPN);
rowtemp.CreateCell(3).SetCellValue(result[i].MIProductName);
rowtemp.CreateCell(4).SetCellValue(result[i].MIInjectionMC);
rowtemp.CreateCell(5).SetCellValue(result[i].MICustomerPN);
rowtemp.CreateCell(6).SetCellValue(Convert.ToDouble(result[i].MICavity));
rowtemp.CreateCell(7).SetCellValue(Convert.ToDouble(result[i].MICycletime));
rowtemp.CreateCell(8).SetCellValue(Convert.ToDouble(result[i].MICycletimeCav));
rowtemp.CreateCell(9).SetCellValue(Convert.ToDouble(result[i].MIStandardHeadcount));
rowtemp.CreateCell(10).SetCellValue(result[i].MTStandardScrap);
rowtemp.CreateCell(11).SetCellValue(result[i].MIMaterialPN);
rowtemp.CreateCell(12).SetCellValue(Convert.ToDouble(result[i].MICavityG));
rowtemp.CreateCell(13).SetCellValue(result[i].MIMoldNo);
rowtemp.CreateCell(14).SetCellValue(result[i].MIAssAC);
rowtemp.CreateCell(15).SetCellValue(result[i].MIWorkOrder);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
var exportFileName = string.Format("{0}{1}.xls", "MaterialInfo", DateTime.Now.ToString("yyyyMMddHHmmss"));
return(File(ms, "application/vnd.ms-excel", exportFileName));
}
public ActionResult WorkOrderExport(MaterialOtherSearchModel model)
{
model.PageSize = 1000;
var totalCount = 0;
var result = MaterialBusiness.MaterialOtherSearch(model, 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("Order No");
row1.CreateCell(1).SetCellValue("Sap No");
row1.CreateCell(2).SetCellValue("Product ");
row1.CreateCell(3).SetCellValue("Receipt Time");
row1.CreateCell(4).SetCellValue("Receipt By");
row1.CreateCell(5).SetCellValue("Close Date/Shift");
row1.CreateCell(6).SetCellValue("Order Archived");
row1.CreateCell(7).SetCellValue("Parameter Record");
row1.CreateCell(8).SetCellValue("Maintenance Record");
row1.CreateCell(9).SetCellValue("Tool Machine Setup Check List ");
row1.CreateCell(10).SetCellValue("Quantity Confirm");
row1.CreateCell(11).SetCellValue("Archived By");
row1.CreateCell(12).SetCellValue("Weekly Check");
row1.CreateCell(13).SetCellValue("Remarks");
row1.CreateCell(14).SetCellValue("GetBy");
row1.CreateCell(15).SetCellValue("GetTime");
for (int i = 0; i < result.Count(); i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(result[i].WIWorkOrder);
rowtemp.CreateCell(1).SetCellValue(result[i].WISapPN);
rowtemp.CreateCell(2).SetCellValue(result[i].WIProductName);
rowtemp.CreateCell(3).SetCellValue(result[i].WIReceiptTime);
rowtemp.CreateCell(4).SetCellValue(result[i].WIReceiptBy);
rowtemp.CreateCell(5).SetCellValue(result[i].WICloseDateShift);
rowtemp.CreateCell(6).SetCellValue(result[i].WIOrderArchived);
rowtemp.CreateCell(7).SetCellValue(result[i].WIParameterRecord);
rowtemp.CreateCell(8).SetCellValue(result[i].WIToolMaintenanceRecord);
rowtemp.CreateCell(9).SetCellValue(result[i].WIToolMachineCheck);
rowtemp.CreateCell(10).SetCellValue(result[i].WIQuantityConfirm);
rowtemp.CreateCell(11).SetCellValue(result[i].WIArchivedBy);
rowtemp.CreateCell(12).SetCellValue(result[i].WIWeeklyCheck);
rowtemp.CreateCell(13).SetCellValue(result[i].WIRemarks);
rowtemp.CreateCell(14).SetCellValue(result[i].WIGetBy);
rowtemp.CreateCell(15).SetCellValue(result[i].WIGetTime);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
var exportFileName = string.Format("{0}{1}.xls", "WorkOrderInfo", DateTime.Now.ToString("yyyyMMddHHmmss"));
return(File(ms, "application/vnd.ms-excel", exportFileName));
}
/// <summary>
/// NPOI插件保存excel到网页，用于MVC4
/// </summary>
/// <returns></returns>
public ActionResult DownLoadExcel()
{
string Path = AppDomain.CurrentDomain.BaseDirectory + "Resource\\Config\\DatetimeConfig.xml";
XElement xele = XElement.Load(Path);
if ((string)xele.Attribute("content") != "" && (string)xele.Attribute("DateBegin") != "" && (string)xele.Attribute("DateEnd") != "")
{
DateTime dateTime = DateTime.Parse((string)xele.Attribute("content"));
DateTime dataTimeBegin = DateTime.Parse((string)xele.Attribute("DateBegin"));
DateTime dataTimeEnd = DateTime.Parse((string)xele.Attribute("DateEnd"));
TimeSpan span = DateTime.Now - dateTime;
if (span.TotalMinutes < 30)
{
//Response.Redirect("/Home/Index");
return(this.Content("<script>alert('30分钟内不能重复导出到excel!')</script>"));
}
if (DateTime.Now < dataTimeBegin || DateTime.Now > dataTimeEnd)
{
return(this.Content("<script>alert('只能再指定的时间内导出到excel!')</script>"));
}
}
//延迟30分钟再可以提交
string sql = "SELECT [UserID],[UserCode],[UserName],[ParentID],[Position],[Mobile],[Email],[Levels],[AttentionTime]FROM Users WHERE CreateStatus = 1 AND(AttentionState = 1); ";
DataTable dt = SqlExportService.GetDataTableFromSql(Request, sql);
//创建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.RowStyle.FillBackgroundColor = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
}
}
string strdate = DateTime.Now.ToString("yyyyMMddhhmmss");//获取当前时间
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
DateTime = DateTime.Now;
xele.SetAttributeValue("content", DateTime);
xele.Save(AppDomain.CurrentDomain.BaseDirectory + "Resource\\Config\\DatetimeConfig.xml");
return(File(ms, "application/vnd.ms-excel", strdate + "Excel.xls"));
}
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 Excel(T_OutStockTaskInfo model)
{
string strErrMsg = string.Empty;
List <T_OutStockTaskDetailsInfo> lstExport = new List <T_OutStockTaskDetailsInfo>();
tfunc_detail.GetExportTaskDetail(model, ref lstExport, ref strErrMsg);
//创建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("ERP单号");
row1.CreateCell(4).SetCellValue("物料名称");
row1.CreateCell(3).SetCellValue("物料号");
row1.CreateCell(4).SetCellValue("物料名称");
row1.CreateCell(5).SetCellValue("任务数");
row1.CreateCell(6).SetCellValue("剩余数");
row1.CreateCell(9).SetCellValue("下架数");
row1.CreateCell(7).SetCellValue("操作人");
row1.CreateCell(8).SetCellValue("操作时间");
row1.CreateCell(9).SetCellValue("创建人");
row1.CreateCell(10).SetCellValue("创建时间");
row1.CreateCell(11).SetCellValue("供应商编号");
row1.CreateCell(12).SetCellValue("供应商");
row1.CreateCell(13).SetCellValue("状态");
//row1.CreateCell(14).SetCellValue("拣货车");
//row1.CreateCell(15).SetCellValue("地标");
//将数据逐步写入sheet1各个行
for (int i = 0; i < lstExport.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(lstExport[i].TaskNo);
rowtemp.CreateCell(1).SetCellValue(lstExport[i].StrVoucherType);
rowtemp.CreateCell(2).SetCellValue(lstExport[i].ErpVoucherNo);
rowtemp.CreateCell(3).SetCellValue(lstExport[i].MaterialNo);
rowtemp.CreateCell(4).SetCellValue(lstExport[i].MaterialDesc);
rowtemp.CreateCell(5).SetCellValue(lstExport[i].TaskQty.ToString());
rowtemp.CreateCell(6).SetCellValue(lstExport[i].RemainQty.ToString());
rowtemp.CreateCell(7).SetCellValue(lstExport[i].UnShelveQty.ToString());
rowtemp.CreateCell(8).SetCellValue(lstExport[i].OperatorUserName.ToString());
rowtemp.CreateCell(9).SetCellValue(lstExport[i].OperatorDateTime.ToString());
rowtemp.CreateCell(10).SetCellValue(lstExport[i].Creater.ToString());
rowtemp.CreateCell(11).SetCellValue(lstExport[i].CreateTime.ToString());
rowtemp.CreateCell(12).SetCellValue(lstExport[i].SupCusCode);
rowtemp.CreateCell(13).SetCellValue(lstExport[i].StrStatus);
//rowtemp.CreateCell(14).SetCellValue(lstExport[i].CarNo);
}
// 写入到客户端
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"));
}
protected NPOI.SS.UserModel.ICell GetCellSure(NPOI.SS.UserModel.IRow row, int index, System.Type type)
{
var cell = row.GetCell(index);
if (cell == null)
{
cell = row.CreateCell(index, Type2CellType(type));
}
return(cell);
}
private void FillObjectList2Row(System.Collections.IList lst, NPOI.SS.UserModel.IRow row, System.Type type)
{
var jmp = GetCellSure(row, 0, typeof(string));
for (int i = 0; i < lst.Count; i++)
{
var obj = lst[i];
Object2Cell(null, obj, GetCellSure(row, i + 1, type));
}
}
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));
}
/// <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();
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="jsonString"></param>
/// <returns></returns>
public FileResult Excel(string jsonString)
{
T_StockInfoEX model = JsonConvert.DeserializeObject <T_StockInfoEX>(jsonString);
string strErrMsg = string.Empty;
List <T_StockInfoEX> lstExport = new List <T_StockInfoEX>();
string strError = "";
DividPage page = new DividPage
{
CurrentPageShowCounts = 1000000
};
queryDB.GetStockDetInfo(model, ref page, ref lstExport, ref strError);
//创建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("EAN");
row1.CreateCell(4).SetCellValue("效期");
row1.CreateCell(5).SetCellValue("序列号");
row1.CreateCell(6).SetCellValue("批次");
row1.CreateCell(7).SetCellValue("仓库");
row1.CreateCell(8).SetCellValue("库区");
row1.CreateCell(9).SetCellValue("库位");
row1.CreateCell(10).SetCellValue("数量");
row1.CreateCell(11).SetCellValue("项目跟踪号");
//将数据逐步写入sheet1各个行
for (int i = 0; i < lstExport.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(lstExport[i].StrongHoldCode == null ? "" : lstExport[i].StrongHoldCode.ToString());
rowtemp.CreateCell(1).SetCellValue(lstExport[i].MaterialNo == null ? "" : lstExport[i].MaterialNo.ToString());
rowtemp.CreateCell(2).SetCellValue(lstExport[i].MaterialDesc == null ? "" : lstExport[i].MaterialDesc.ToString());
rowtemp.CreateCell(3).SetCellValue(lstExport[i].EAN == null ? "" : lstExport[i].EAN.ToString());
rowtemp.CreateCell(4).SetCellValue(lstExport[i].EDate == null ? "" : lstExport[i].EDate.ToString());
rowtemp.CreateCell(5).SetCellValue(lstExport[i].SerialNo == null ? "" : lstExport[i].SerialNo.ToString());
rowtemp.CreateCell(6).SetCellValue(lstExport[i].BatchNo == null ? "" : lstExport[i].BatchNo.ToString());
rowtemp.CreateCell(7).SetCellValue(lstExport[i].WarehouseNo == null ? "" : lstExport[i].WarehouseNo.ToString());
rowtemp.CreateCell(8).SetCellValue(lstExport[i].HouseNo == null ? "" : lstExport[i].HouseNo.ToString());
rowtemp.CreateCell(9).SetCellValue(lstExport[i].AreaNo == null ? "" : lstExport[i].AreaNo.ToString());
rowtemp.CreateCell(10).SetCellValue(lstExport[i].Qty.ToString());
rowtemp.CreateCell(11).SetCellValue(lstExport[i].TracNo == null ? "" : lstExport[i].TracNo.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"));
}
internal void CreateRow()
{
++currentRowNumber;
currentRow=currentSheet.CreateRow(currentRowNumber);
}
