private void ExportToFile()
{
if (DialogResult.OK == this.folderBrowserDialog1.ShowDialog())
{
var path = this.folderBrowserDialog1.SelectedPath;
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
var sheet = workbook.CreateSheet(this.dbObject.Name);
var headerRow = sheet.CreateRow(0);
for (var i = 0; i < this.dtDataResult.Columns.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(this.dtDataResult.Columns[i].ColumnName);
}
for (var i = 0; i < this.dtDataResult.Rows.Count; i++)
{
var newRow = sheet.CreateRow(i + 1);
for (var j = 0; j < this.dtDataResult.Columns.Count; j++)
{
newRow.CreateCell(j).SetCellValue(this.dtDataResult.Rows[i][j].ToString());
}
}
var filePath = Path.Combine(path, this.dbObject.Name + ".xls");
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.Read))
{
workbook.Write(fs);
}
}
}
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));
}
public static void WriteXlsx(string file, string[][] contents)
{
IWorkbook book = null;
if (file.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
ISheet sheet = book.CreateSheet("Sheet1");
for (int i = 0; i < contents.Length; i++)
{
var row = sheet.CreateRow(i);
for (int k = 0; k < contents[i].Length; k++)
{
var cell = row.CreateCell(k, CellType.String);
cell.SetCellValue(contents[i][k]);
}
}
using (FileStream fs = new FileStream(file, FileMode.Create))
{
book.Write(fs);
}
}
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 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 static void WriteXlsx(string file, Dictionary <string, string[][]> sheetDatas)
{
IWorkbook book = null;
if (file.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
else
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
foreach (var pair in sheetDatas)
{
ISheet sheet = book.CreateSheet(pair.Key);
for (int i = 0; i < pair.Value.Length; i++)
{
var row = sheet.CreateRow(i);
for (int k = 0; k < pair.Value[i].Length; k++)
{
var cell = row.CreateCell(k, CellType.String);
cell.SetCellValue(pair.Value[i][k]);
}
}
using (FileStream fs = new FileStream(file, FileMode.Create))
{
book.Write(fs);
}
}
}
private void createWorkSheetPeriodRow(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet,
DateTime periodFromDate, DateTime periodToDate)
{
// create header styles
NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleLeft = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
NPOI.HSSF.UserModel.HSSFFont HeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
HeaderFont.Boldweight = 900;
HeaderFont.FontHeightInPoints = 16;
HeaderStyleLeft.SetFont(HeaderFont);
NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
HeaderStyleCenter.CloneStyleFrom(HeaderStyleLeft);
HeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleRight = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
HeaderStyleRight.CloneStyleFrom(HeaderStyleLeft);
HeaderStyleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
// Create header row
NPOI.HSSF.UserModel.HSSFRow HeaderRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0);
NPOI.HSSF.UserModel.HSSFCell HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(5);
HeaderCell.SetCellValue(periodFromDate.ToString("dd-MMM-yy"));
HeaderCell.CellStyle = HeaderStyleRight;
HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(6);
HeaderCell.SetCellValue("~");
HeaderCell.CellStyle = HeaderStyleCenter;
HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(7);
HeaderCell.SetCellValue(periodToDate.ToString("dd-MMM-yy"));
HeaderCell.CellStyle = HeaderStyleLeft;
}
public ICellStyle Cellstyle()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
IFont font = book.CreateFont();
font.FontName = "宋体";
//font.setFontHeightInPoints((short)10);
font.FontHeightInPoints = 10;
// 普通单元格样式
ICellStyle style = book.CreateCellStyle();
style.SetFont(font);
//style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
//style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
//style.setWrapText(true);
//style.setLeftBorderColor(HSSFColor.BLACK.index);
//style.setBorderLeft((short)1);
//style.setRightBorderColor(HSSFColor.BLACK.index);
//style.setBorderRight((short)1);
//style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
//style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
//style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．
return(style);
}
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(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")));
}
}
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));
}
/// <summary>
/// 模板导入到NPOI Workbook中
/// </summary>
/// <param name="dataSourceFilePath">数据源路经</param>
/// <returns></returns>
private NPOI.HSSF.UserModel.HSSFWorkbook InitializeWorkbook(string dataSourceFilePath)
{
try
{
NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook = null;
System.IO.FileStream file = new System.IO.FileStream(dataSourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
if (null == file)
{
return(hssfworkbook);
}
hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
if (null == hssfworkbook)
{
return(hssfworkbook);
}
//create a entry of DocumentSummaryInformation
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "test";
hssfworkbook.DocumentSummaryInformation = dsi;
//create a entry of SummaryInformation
NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
si.Subject = "test";
hssfworkbook.SummaryInformation = si;
return(hssfworkbook);
}
catch (Exception ex)
{
return(null);
throw new Exception(ex.ToString());
}
}
/*
* 作用：将DataTable数据表转Excel的方法，数据存在内存中，返回内存流
* 1、创建workbook
* 2、用workbook创建sheet
* 3、用sheet创建row
* 4、用row创建cell
* 5、给这个cell设置值
* 6、将整个workbook写入流
*
* ***/
public System.IO.MemoryStream DataTableToExcel(DataTable dt)
{
int rows = dt.Rows.Count; //数据表格的总行数
int cols = dt.Columns.Count; //数据表格的总列数
//1、创建book对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//2、创建sheet对象
var sheet = book.CreateSheet("sheet名称");
//3、创建row（行）对象
//sheet.CreateRow(0)
for (int r = 0; r < rows; r++)
{
var row = sheet.CreateRow(r);
for (int c = 0; c < cols; c++)
{
//4、创建col（列）对象
var col = row.CreateCell(c);
//5、为这行这列填充数据
col.SetCellValue(Convert.ToString(dt.Rows[r][c]));
}
}
//6、将整个book写入流
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
return(ms);
}
internal static NPOI.SS.UserModel.IWorkbook CreateWorkbookFromDictionary(string sheetName, Dictionary <string, string> values)
{
if (string.IsNullOrEmpty(sheetName))
{
throw new ArgumentNullException("sheetName");
}
if (values == null)
{
throw new ArgumentNullException("values");
}
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
var worksheet = workbook.CreateSheet(sheetName);
int column = 0;
int row = 0;
foreach (var pair in values)
{
var currentRow = worksheet.CreateRow(row);
var leftCell = currentRow.CreateCell(column);
leftCell.SetCellValue(pair.Key);
var rightCell = currentRow.CreateCell(column + 1);
rightCell.SetCellValue(pair.Value);
row++;
}
return(workbook);
}
public void SaveToExcel(DataTable dt, string filePath)
{
if (filePath.IsNotEmpty() && null != dt && dt.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet(dt.TableName);
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
dataRow.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;
}
}
}
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 匯出客戶銀行資訊()
{
//建立Excel文件
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//新增sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//取得匯出資料List
List <客戶銀行資訊> dataList = repo客戶銀行資訊.All().ToList();
//给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("客戶名稱");
//將資料逐筆寫入
for (int i = 0; i < dataList.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(dataList[i].銀行名稱.ToString());
rowtemp.CreateCell(1).SetCellValue(dataList[i].銀行代碼.ToString());
rowtemp.CreateCell(2).SetCellValue(dataList[i].分行代碼.ToString());
rowtemp.CreateCell(3).SetCellValue(dataList[i].帳戶名稱.ToString());
rowtemp.CreateCell(4).SetCellValue(dataList[i].帳戶號碼.ToString());
rowtemp.CreateCell(5).SetCellValue(dataList[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", "客戶銀行資訊清單.xls"));
}
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 static void WriteExcel(DataGridView dg, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && dg != null && dg.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("DG");
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dg.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dg.Columns[i].HeaderText);
}
for (int i = 0; i < dg.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dg.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dg[j, i].Value));
}
}
// 写入到客户端.
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);
}
}
public static void ExportXLS(DataTable dt, string[] columns, string fileName)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet1");
NPOI.SS.UserModel.IRow row0 = sheet.CreateRow(0);
for (int i = 0; i < columns.Length; i++)
{
row0.CreateCell(i).SetCellValue(columns[i]);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < columns.Length; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", fileName));
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
/// <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 ExportCustomers()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Customers");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
var customers = 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 < customers.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(customers[i].客戶名稱.ToString());
rowtemp.CreateCell(1).SetCellValue(customers[i].統一編號.ToString());
rowtemp.CreateCell(2).SetCellValue(customers[i].電話.ToString());
rowtemp.CreateCell(3).SetCellValue(customers[i].傳真.ToString());
rowtemp.CreateCell(4).SetCellValue(customers[i].地址.ToString());
rowtemp.CreateCell(5).SetCellValue(customers[i].Email.ToString());
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "Customers.xls"));
}
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 void ExportDataToExcelByWeb(System.Data.DataTable dt, string FileName)
{
string[] headerList = new string[dt.Columns.Count];
string[] headerCode = new string[dt.Columns.Count];//对应表头的字段
for (int i = 0; i < dt.Columns.Count; i++)
{
headerList.SetValue(dt.Columns[i].ColumnName, i);
headerCode.SetValue(dt.Columns[i].ColumnName, i);
}
//如果是linq查询的列表，需要转datatable
NPOI.HSSF.UserModel.HSSFWorkbook book = ExportExcel.Export(dt, headerList, headerCode);
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
string UserAgent = Request.ServerVariables["http_user_agent"].ToLower();
if (UserAgent.IndexOf("firefox") == -1)//火狐浏览器
{
FileName = HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
}
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", FileName));
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
/// <summary>
/// IQC 导出Excel 数据流
/// </summary>
/// <param name="dataSource">数据源</param>
/// <returns></returns>
public System.IO.MemoryStream ExportPrintToExcel(List <SampleItemsIqcRecordModel> dataSource)
{
try
{
//数据为Null时返回数值
System.IO.MemoryStream stream = new System.IO.MemoryStream();
if (dataSource == null || dataSource.Count == 0)
{
return(stream);
}
string filePath = System.IO.Path.GetFullPath(PrintSampleModel(dataSource));
NPOI.HSSF.UserModel.HSSFWorkbook workbook = InitializeWorkbook(filePath);
if (workbook == null)
{
return(null);
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
sheet.ForceFormulaRecalculation = true;
//保存
Store(dataSource);
return(stream);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
protected static MemoryStream GetMS(List <FishEntity.CompanyEntity> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.HeightInPoints = 26;
CreateCell(row, 0, "客户市场需求预测表", CellType.String, cellStyle);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12));
row = sheet.CreateRow(1);
cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold);
CreateCell(row, 0, "客户编号", CellType.String, cellStyle);
CreateCell(row, 1, "客户名称", CellType.String, cellStyle);
CreateCell(row, 2, "类别", CellType.String, cellStyle);
CreateCell(row, 3, "综合等级", CellType.String, cellStyle);
CreateCell(row, 4, "需求量等级", CellType.String, cellStyle);
CreateCell(row, 5, "活跃程度", CellType.String, cellStyle);
CreateCell(row, 6, "忠诚度", CellType.String, cellStyle);
CreateCell(row, 7, "主要产品", CellType.String, cellStyle);
CreateCell(row, 8, "业务员", CellType.String, cellStyle);
CreateCell(row, 9, "联系人", CellType.String, cellStyle);
CreateCell(row, 10, "最近联系日期", CellType.String, cellStyle);
CreateCell(row, 11, "最近周预估", CellType.String, cellStyle);
CreateCell(row, 12, "最近月预估", CellType.String, cellStyle);
if (list != null && list.Count > 0)
{
int rowidx = 1;
foreach (FishEntity.CompanyEntity model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.code);
CreateCell(row, 1, model.fullname);
CreateCell(row, 2, model.type);
CreateCell(row, 3, model.generallevel);
CreateCell(row, 4, model.requiredlevel);
CreateCell(row, 5, model.managestandard);
CreateCell(row, 6, model.activelevel);
CreateCell(row, 7, model.products);
CreateCell(row, 8, model.salesman);
CreateCell(row, 9, model.linkman);
CreateCell(row, 10, model.currentlink);
CreateCell(row, 11, model.currentweekestimate);
CreateCell(row, 12, model.currentmonthestimate);
# endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
/// <summary>
/// 通过NPOI导出excel
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
public static void WriteExcel(DataTable dt, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && null != dt && 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;
}
}
}
private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
{
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(sheetName);
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in sourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
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"));
}
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(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"));
}
}
void CreateExcel(object sender)
{
try
{
string filePath = Application.StartupPath + "\\DailyPOSSales.xls";
if (System.IO.File.Exists(filePath))
{
File.Delete(filePath);
}
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Daily POS Sales");
//第1行
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICell fistCell = row.CreateCell(0);
fistCell.SetCellValue("Daily POS Sales");
ICellStyle firstRowStyle = workbook.CreateCellStyle();
firstRowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
IFont firstRowFont = workbook.CreateFont();
firstRowFont.FontName = "Tahoma";
firstRowFont.FontHeightInPoints = 20;
firstRowFont.Color = HSSFColor.Blue.Index;
firstRowFont.Boldweight = short.MaxValue;
firstRowStyle.SetFont(firstRowFont);
fistCell.CellStyle = firstRowStyle;
row.HeightInPoints = 25;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 15));
using (FileStream fs = File.OpenWrite(filePath))
{
workbook.Write(fs);
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
//this.Invoke(new Action(() =>
//{
// (sender as Button).Enabled = true;
//}));
}
}
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 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 ActionResult MaterialExport(string queryWord, string queryType, string queryStarDocnumber, string queryEndDocnumber, string zStatus, string dStatus, string wStatus, string queryDescribe)
{
List<ImOrExMaterialDefine> list = new List<ImOrExMaterialDefine>();
NPOI.SS.UserModel.IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
try
{
ISheet sheet = wb.CreateSheet("Sheet1");
sheet.SetColumnWidth(0, 20 * 256);
sheet.SetColumnWidth(1, 13 * 256);
sheet.SetColumnWidth(2, 30 * 256);
sheet.SetColumnWidth(3, 15 * 256);
sheet.SetColumnWidth(4, 15 * 256);
sheet.SetColumnWidth(5, 15 * 256);
int iRow = 0;
NPOI.SS.UserModel.IRow titleRow = sheet.CreateRow(0);
titleRow.Height = 20 * 20;
for (int i = 0; i < ImOrExMaterialDefine.GetList().Count; i++)
{
titleRow.CreateCell(i).SetCellValue(ImOrExMaterialDefine.DisplayText(ImOrExMaterialDefine.GetList()[i].ToString()));
}
YnBaseDal.YnPage ynPage = new YnBaseDal.YnPage();
List<AscmMaterialItem> listAscmMaterialItem = AscmMaterialItemService.GetInstance().GetList(ynPage, "", "", queryWord, queryType, queryStarDocnumber, queryEndDocnumber, zStatus, dStatus, wStatus, queryDescribe);
if (listAscmMaterialItem != null && listAscmMaterialItem.Count > 0)
{
ImOrExMaterialDefine titleExportMaterial = new ImOrExMaterialDefine();
List<string> titleCols = ImOrExMaterialDefine.GetList();
titleExportMaterial.materialDocnumber = ImOrExMaterialDefine.DisplayText(titleCols[0]);
titleExportMaterial.wipSupplyType = ImOrExMaterialDefine.DisplayText(titleCols[1]);
titleExportMaterial.materialDescription = ImOrExMaterialDefine.DisplayText(titleCols[2]);
titleExportMaterial.zMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[3]);
titleExportMaterial.dMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[4]);
titleExportMaterial.wMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[5]);
titleExportMaterial.rowNumber = iRow++;
list.Add(titleExportMaterial);
foreach (AscmMaterialItem ascmMaterialItem in listAscmMaterialItem)
{
ImOrExMaterialDefine contentExportMaterial = new ImOrExMaterialDefine();
contentExportMaterial.materialDocnumber = ascmMaterialItem.docNumber;
contentExportMaterial.wipSupplyType = AscmMaterialItem.WipSupplyTypeDefine.DisplayText(ascmMaterialItem.wipSupplyType);
contentExportMaterial.materialDescription = ascmMaterialItem.description;
contentExportMaterial.zMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.zMtlCategoryStatus);
contentExportMaterial.dMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.dMtlCategoryStatus);
contentExportMaterial.wMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.wMtlCategoryStatus);
contentExportMaterial.rowNumber = iRow++;
list.Add(contentExportMaterial);
}
}
if (list != null && list.Count > 0)
{
foreach (ImOrExMaterialDefine ExportMaterial in list)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(ExportMaterial.rowNumber);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue(ExportMaterial.materialDocnumber);
row.CreateCell(1).SetCellValue(ExportMaterial.wipSupplyType);
row.CreateCell(2).SetCellValue(ExportMaterial.materialDescription);
row.CreateCell(3).SetCellValue(ExportMaterial.zMtlCategoryStatus);
row.CreateCell(4).SetCellValue(ExportMaterial.dMtlCategoryStatus);
row.CreateCell(5).SetCellValue(ExportMaterial.wMtlCategoryStatus);
}
}
}
catch (Exception ex)
{
throw ex;
}
byte[] buffer = new byte[] { };
using (System.IO.MemoryStream stream = new MemoryStream())
{
wb.Write(stream);
buffer = stream.GetBuffer();
}
return File(buffer, "application/vnd.ms-excel", "备料形式维护.xls");
}
private void SaveExcel(List<FileProperty> flist)
{
saveFileDialog1.OverwritePrompt = true;
saveFileDialog1.AddExtension = true;
saveFileDialog1.DefaultExt = ".xls";
saveFileDialog1.AutoUpgradeEnabled = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
toolStripStatusLabel1.Text = "正在生成Excel文件";
string path = saveFileDialog1.FileName;
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = workBook.CreateSheet("Sheet1");
NPOI.SS.UserModel.Row row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("Title");
row1.CreateCell(1).SetCellValue("FilePath");
row1.CreateCell(2).SetCellValue("Description");
row1.CreateCell(3).SetCellValue("Tags");
row1.CreateCell(4).SetCellValue("Price");
row1.CreateCell(5).SetCellValue("CateId");
row1.CreateCell(6).SetCellValue("Test");
for(int i=0;i<flist.Count;i++)
{
NPOI.SS.UserModel.Row r = sheet.CreateRow(i + 1);
r.CreateCell(0).SetCellValue(flist[i].Name);
r.CreateCell(1).SetCellValue(flist[i].Path);
}
using (FileStream fs = new FileStream(path, FileMode.Create))
{
workBook.Write(fs);
}
toolStripStatusLabel1.Text = "生成Excel成功";
}
}
private void Relatorio_GerarArquivo(System.Data.DataSet dados, String path, String fileName, String template)
{
#region Declaração de variáveis
Boolean sucesso = false;
System.IO.FileStream file = null;
System.IO.FileStream fileTemplate = null;
NPOI.HSSF.UserModel.HSSFWorkbook xlPackage = null;
#endregion
try
{
#region Inicialização de objetos
fileTemplate = new System.IO.FileStream(path + template, System.IO.FileMode.Open, System.IO.FileAccess.Read);
#endregion
#region Gera o arquivo do relatório
xlPackage = new NPOI.HSSF.UserModel.HSSFWorkbook(fileTemplate);
this.Relatorio_PreencherDados(xlPackage, dados.Tables[0], "Processos");
this.Relatorio_PreencherDados(xlPackage, dados.Tables[1], "Pedidos");
this.Relatorio_PreencherDados(xlPackage, dados.Tables[2], "Escritório");
this.Relatorio_PreencherDados(xlPackage, dados.Tables[3], "Risco x Valor");
this.Relatorio_PreencherDados(xlPackage, dados.Tables[4], "Tipo Especialidade x Valor");
this.Relatorio_PreencherDados(xlPackage, dados.Tables[5], "Escritório x Valor");
this.Relatorio_PreencherDados(xlPackage, dados.Tables[6], "Top 50 Processos");
this.Relatorio_PreencherDados(xlPackage, dados.Tables[7], "Top 20 Assuntos");
this.Relatorio_PreencherDados(xlPackage, dados.Tables[8], "Diferença ICMS-SP x Selic");
this.Relatorio_PreencherDados(xlPackage, dados.Tables[9], "Processos Decaídos");
file = new System.IO.FileStream(path + fileName, System.IO.FileMode.Create);
xlPackage.Write(file);
file.Close();
#endregion
}
catch (Exception ex)
{
#region Tratamento de erro
throw new Exception("MMAA.Pedidos.Web.WS.Pedido.Relatorio_GerarArquivo: " + ex.Message);
#endregion
}
}
private void RelatorioGerencial_GerarArquivo(System.Data.DataSet dados, String path, String fileName, String template)
{
#region Declaração de variáveis
Boolean sucesso = false;
System.IO.FileStream file = null;
System.IO.FileStream fileTemplate = null;
NPOI.HSSF.UserModel.HSSFWorkbook xlPackage = null;
#endregion
try
{
#region Inicialização de objetos
fileTemplate = new System.IO.FileStream(path + template, System.IO.FileMode.Open, System.IO.FileAccess.Read);
#endregion
#region Gera o arquivo do relatório
xlPackage = new NPOI.HSSF.UserModel.HSSFWorkbook(fileTemplate);
this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[0], "Consolidado Geral");
this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[1], "Abertura de Pastas (Workflow)");
this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[2], "Abertura de Pastas DC");
this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[3], "Bloqueado Atual");
this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[4], "Workflow de Documentos");
this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[5], "Documentos Vinculados a Pasta");
this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[6], "Audiência por Data");
this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[7], "SS com Pasta por data Upload");
this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[8], "DC Prazo por Usuário x Cliente");
file = new System.IO.FileStream(path + fileName, System.IO.FileMode.Create);
xlPackage.Write(file);
file.Close();
#endregion
}
catch (Exception ex)
{
#region Tratamento de erro
throw new Exception("MMAA.Pedidos.Web.WS.Pedido.RelatorioGerencial_GerarArquivo: " + ex.Message);
#endregion
}
}
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"));
}
}
public FileResult Export(string Province, string City, string Country, string StoreId, string StartDate, string EndDate, int pi = 1)
{
using (DBContext db = new DBContext())
{
var storeQuery = db.Store.AsQueryable();
if (!string.IsNullOrEmpty(Province)) { storeQuery = storeQuery.Where(q => q.Province.Equals(Province)); }
if (!string.IsNullOrEmpty(City)) { storeQuery = storeQuery.Where(q => q.City.Equals(City)); }
if (!string.IsNullOrEmpty(Country)) { storeQuery = storeQuery.Where(q => q.Country.Equals(Country)); }
if (!string.IsNullOrEmpty(StoreId)) { storeQuery = storeQuery.Where(q => q.ID.Equals(StoreId)); }
var offQuery = db.OfflineSell.AsQueryable();
var olQuery = 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;
}
offQuery = offQuery.Where(q => q.XFRQ.CompareTo(start) > 0 && q.XFRQ.CompareTo(end) < 0 && q.bFinish == Finish.YJS);
olQuery = olQuery.Where(q => q.CreateTime.CompareTo(start) > 0 && q.CreateTime.CompareTo(end) < 0 && q.Status == 5);
var list = (from q in storeQuery
join off in offQuery on q.ID equals off.StationID into off_join
from o in off_join.DefaultIfEmpty()
join ol in olQuery on q.ID equals ol.StoreId into ol_join
from l in ol_join.DefaultIfEmpty()
group new { q.StoreName, o.JE, l.Payable } by new { q.StoreName } into s
select new PSJE() { StoreName = s.Key.StoreName, Pay = s.Sum(p => (p.JE == null ? 0 : p.JE) + (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"));
}
}
public FileResult ExportStore(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 orderQuery
join t in storeQuery on q.StoreId equals t.ID
group q by new { q.StoreId, q.StoreName } into s
select new PSJE() { StoreName = s.Key.StoreName, Pay = s.Sum(p => 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());
}
DateTime now = DateTime.Now;
// 写入到客户端
return ExportExcel(book, now.ToString("yyMMddHHmmssfff"));
}
}
// 送货合单查询报表导出
public ActionResult DeliveryBatSumExport(int? supplierId, string queryWord,
string startCreateTime, string endCreateTime, string status, int? driverId, string batchSumBarCode)
{
IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
sheet.SetColumnWidth(0, 16 * 256);
sheet.SetColumnWidth(1, 14 * 256);
sheet.SetColumnWidth(2, 26 * 256);
sheet.SetColumnWidth(3, 10 * 256);
sheet.SetColumnWidth(4, 17 * 256);
sheet.SetColumnWidth(5, 17 * 256);
sheet.SetColumnWidth(6, 10 * 256);
sheet.SetColumnWidth(7, 12 * 256);
sheet.SetColumnWidth(8, 10 * 256);
sheet.SetColumnWidth(9, 17 * 256);
sheet.SetColumnWidth(10, 17 * 256);
sheet.SetColumnWidth(11, 17 * 256);
IRow titleRow = sheet.CreateRow(0);
titleRow.Height = 20 * 20;
titleRow.CreateCell(0).SetCellValue("合单号");
titleRow.CreateCell(1).SetCellValue("司机编号");
titleRow.CreateCell(2).SetCellValue("供应商名称");
titleRow.CreateCell(3).SetCellValue("状态");
titleRow.CreateCell(4).SetCellValue("预约开始时间");
titleRow.CreateCell(5).SetCellValue("预约最后时间");
titleRow.CreateCell(6).SetCellValue("总数量");
titleRow.CreateCell(7).SetCellValue("容器绑定数量");
titleRow.CreateCell(8).SetCellValue("确认人");
titleRow.CreateCell(9).SetCellValue("确认时间");
titleRow.CreateCell(10).SetCellValue("到厂时间");
titleRow.CreateCell(11).SetCellValue("接收时间");
string fileDownloadName = "供应商送货合单查询报表";
string whereOther = "", whereSupplier = "", whereStatus = "";
string whereStartCreateTime = "", whereEndCreateTime = "", whereDriver = "";
if (supplierId.HasValue)
whereSupplier = " supplierId=" + supplierId.Value;
if (!string.IsNullOrEmpty(status))
{
foreach (string itemStatus in status.Split(','))
{
if (!string.IsNullOrEmpty(whereStatus))
whereStatus += " or ";
whereStatus += " status='" + itemStatus + "'";
}
}
if (driverId.HasValue)
whereDriver = " driverId=" + driverId.Value;
DateTime dtStartCreateTime, dtEndCreateTime;
if (!string.IsNullOrEmpty(startCreateTime) && DateTime.TryParse(startCreateTime, out dtStartCreateTime))
whereStartCreateTime = "createTime>='" + dtStartCreateTime.ToString("yyyy-MM-dd 00:00:00") + "'";
if (!string.IsNullOrEmpty(endCreateTime) && DateTime.TryParse(endCreateTime, out dtEndCreateTime))
whereEndCreateTime = "createTime<'" + dtEndCreateTime.AddDays(1).ToString("yyyy-MM-dd 00:00:00") + "'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereSupplier);
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereStatus);
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereDriver);
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereStartCreateTime);
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereEndCreateTime);
if (batchSumBarCode != null && batchSumBarCode.Trim() != "")
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, "barcode='" + batchSumBarCode.Trim() + "'");
List<AscmDeliBatSumMain> list = AscmDeliBatSumMainService.GetInstance().GetList(null, string.Empty, string.Empty, queryWord, whereOther);
if (list != null)
{
int rowIndex = 0;
foreach (AscmDeliBatSumMain deliBatSumMain in list)
{
IRow row = sheet.CreateRow(++rowIndex);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue(deliBatSumMain.docNumber);
row.CreateCell(1).SetCellValue(deliBatSumMain.driverSn);
row.CreateCell(2).SetCellValue(deliBatSumMain.supplierName);
row.CreateCell(3).SetCellValue(deliBatSumMain.statusCn);
row.CreateCell(4).SetCellValue(deliBatSumMain.appointmentStartTimeShow);
row.CreateCell(5).SetCellValue(deliBatSumMain.appointmentEndTimeShow);
row.CreateCell(6).SetCellValue(deliBatSumMain.totalNumber.ToString());
row.CreateCell(7).SetCellValue(deliBatSumMain.containerBindNumber.ToString());
row.CreateCell(8).SetCellValue(deliBatSumMain.confirmor);
row.CreateCell(9).SetCellValue(deliBatSumMain._confirmTime);
row.CreateCell(10).SetCellValue(deliBatSumMain._toPlantTime);
row.CreateCell(11).SetCellValue(deliBatSumMain._acceptTime);
}
}
byte[] buffer = new byte[] { };
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
wb.Write(stream);
buffer = stream.GetBuffer();
}
return File(buffer, "application/vnd.ms-excel", fileDownloadName + ".xls");
}
public FileResult Export清單()
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
List<vw客戶清單> data = repo客戶清單.All().ToList();
//给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 < data.Count(); i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(data[i].客戶名稱);
rowtemp.CreateCell(1).SetCellValue(data[i].聯絡人數量.ToString());
rowtemp.CreateCell(2).SetCellValue(data[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", "客戶清單_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
}
public FileResult Export()
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
List<客戶聯絡人> data = repo.Get客戶聯絡人().ToList();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("職稱");
row1.CreateCell(1).SetCellValue("姓名");
row1.CreateCell(2).SetCellValue("Email");
row1.CreateCell(3).SetCellValue("手機");
row1.CreateCell(4).SetCellValue("電話");
row1.CreateCell(5).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].職稱);
rowtemp.CreateCell(1).SetCellValue(data[i].姓名);
rowtemp.CreateCell(2).SetCellValue(data[i].Email);
rowtemp.CreateCell(3).SetCellValue(data[i].手機);
rowtemp.CreateCell(4).SetCellValue(data[i].電話);
rowtemp.CreateCell(5).SetCellValue(data[i].客戶資料.客戶名稱);
}
// 写入到客户端
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");
}
public static MemoryStream RenderToExcel(List<Bean.ArchiveBean> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
#region head row
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//row.RowStyle = style;
row.Height = 26 * 20;
CreateCell(row, 0, "序号", CellType.String, style);
CreateCell(row, 1, "责任人", CellType.String, style);
CreateCell(row, 2, "文件题目", CellType.String, style);
CreateCell(row, 3, "页数", CellType.String, style);
CreateCell(row, 4, "编号", CellType.String, style);
CreateCell(row, 5, "备注", CellType.String, style);
#endregion
if (list != null && list.Count > 0)
{
int rowidx = 0;
foreach (Bean.ArchiveBean model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.idx);
CreateCell(row, 1, model.manager);
CreateCell(row, 2, model.title);
CreateCell(row, 3, model.pages);
CreateCell(row, 4, model.number);
CreateCell(row, 5, model.remark);
#endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
public string readCellExcel(string filePath, string isheetname, int irow, int icolumn)
{
string result = "";
try
{
using (StreamReader input = new StreamReader(filePath))
{
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new NPOI.POIFS.FileSystem.POIFSFileSystem(input.BaseStream));
if (null == workbook)
{
result = "";
}
NPOI.SS.UserModel.IFormulaEvaluator formulaEvaluator = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workbook);
NPOI.SS.UserModel.DataFormatter dataFormatter = new NPOI.HSSF.UserModel.HSSFDataFormatter(new CultureInfo("vi-VN"));
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(isheetname);
NPOI.SS.UserModel.IRow row = sheet.GetRow(irow);
result = dataFormatter.FormatCellValue(row.Cells[icolumn], formulaEvaluator);
/*foreach (NPOI.SS.UserModel.ISheet sheet in workbook)
{
foreach (NPOI.SS.UserModel.IRow row in sheet)
{
foreach (NPOI.SS.UserModel.ICell cell in row)
{
string value = dataFormatter.FormatCellValue(cell, formulaEvaluator);
}
}
}*/
}
}
catch { }
return result;
}
public static MemoryStream RenderToExcel(List<Models.Beans.Contract> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
#region head row
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
//row.RowStyle = style;
row.Height = 26 * 20;
CreateCell(row, 0, "存放位置", CellType.String, style);
CreateCell(row, 1, "合同号", CellType.String, style);
CreateCell(row, 2, "序号", CellType.String, style);
CreateCell(row, 3, "项目编号", CellType.String, style);
CreateCell(row, 4, "项目名称", CellType.String, style);
CreateCell(row, 5, "项目负责人", CellType.String, style);
CreateCell(row, 6, "联系方式", CellType.String, style);
CreateCell(row, 7, "分管部门", CellType.String, style);
CreateCell(row, 8, "分包名称", CellType.String, style);
CreateCell(row, 9, "分包预算(万元)", CellType.String, style);
CreateCell(row, 10, "招标编号", CellType.String, style);
CreateCell(row, 11, "招标公司", CellType.String, style);
CreateCell(row, 12, "开标时间", CellType.String, style);
CreateCell(row, 13, "付款方式", CellType.String, style);
CreateCell(row, 14, "中标公司名称", CellType.String, style);
CreateCell(row, 15, "联系人", CellType.String, style);
CreateCell(row, 16, "手机号码", CellType.String, style);
CreateCell(row, 17, "中标金额(万元)", CellType.String, style);
CreateCell(row, 18, "签合同日期", CellType.String, style);
CreateCell(row, 19, "交货时间", CellType.String, style);
CreateCell(row, 20, "验收情况", CellType.String, style);
CreateCell(row, 21, "进度", CellType.String, style);
CreateCell(row, 22, "支付全款", CellType.String, style);
CreateCell(row, 23, "押款", CellType.String, style);
CreateCell(row, 24, "退款", CellType.String, style);
CreateCell(row, 25, "标签", CellType.String, style);
#endregion
if (list != null && list.Count > 0)
{
int rowidx = 0;
foreach (Models.Beans.Contract model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.contractplace);
CreateCell(row, 1, model.contractnum);
CreateCell(row, 2, model.seq);
CreateCell(row, 3, model.projectnum);
CreateCell(row, 4, model.projectname);
CreateCell(row, 5, model.projectmanager);
CreateCell(row, 6, model.tel);
CreateCell(row, 7, model.depart);
CreateCell(row, 8, model.packageName);
CreateCell(row, 9, model.packageBudget);
CreateCell(row, 10, model.tendarNum);
CreateCell(row, 11, model.tendarCompany);
CreateCell(row, 12, model.tendarStartTime);
CreateCell(row, 13, model.paymethod);
CreateCell(row, 14, model.bcompany);
CreateCell(row, 15, model.linker);
CreateCell(row, 16, model.phone);
CreateCell(row, 17, model.money);
CreateCell(row, 18, model.signingdate);
CreateCell(row, 19, model.deliveryTime);
CreateCell(row, 20, model.inspection);
CreateCell(row, 21, model.progress);
CreateCell(row, 22, model.isPayAll);
CreateCell(row, 23, model.isArmoured);
CreateCell(row, 24, model.isRefund);
CreateCell(row, 25, model.contractrfid);
#endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
/// <summary>
/// Excel转table 导入 Michaux 20160531
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public DataTable ImportExcelToDataTable(HttpPostedFileBase file,Dictionary<string,ExcelFormatter> formatColumn=null)
{
var datatable = new DataTable();
if (file.FileName.IndexOf("xlsx") > -1)
{
NPOI.XSSF.UserModel.XSSFWorkbook Upfile = new NPOI.XSSF.UserModel.XSSFWorkbook(file.InputStream);
var sheet = Upfile.GetSheetAt(0);
var firstRow = sheet.GetRow(0);
var buffer = new byte[file.ContentLength];
for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++)
{
datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string));
}
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow datarow = datatable.NewRow();
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
bool con = true;
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
//if (formatColumn!=null && formatColumn.ContainsKey(firstRow.GetCell(j).StringCellValue))
//{
//}
var cell = row.GetCell(j);
if (cell == null)
{
datarow[j] = "";
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
datarow[j] = cell.NumericCellValue;
break;
case CellType.String:
datarow[j] = cell.StringCellValue;
break;
case CellType.Blank:
datarow[j] = "";
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
datarow[j] = strFORMULA.ToString();
}
else
{
datarow[j] = null;
}
break;
case CellType.Numeric:
datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
datarow[j] = "";
break;
}
break;
default:
con = false;
break;
}
if (!con)
{
break;
}
}
if (con)
{
datatable.Rows.Add(datarow);
}
}
}
else
{
NPOI.HSSF.UserModel.HSSFWorkbook Upfile = new NPOI.HSSF.UserModel.HSSFWorkbook(file.InputStream);
var sheet = Upfile.GetSheetAt(0);
var firstRow = sheet.GetRow(0);
var buffer = new byte[file.ContentLength];
for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++)
{
datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string));
}
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow datarow = datatable.NewRow();
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
bool con = true;
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
var cell = row.GetCell(j);
if (cell == null)
{
datarow[j] = "";
continue;
}
switch (cell.CellType)
{
case CellType.Numeric:
datarow[j] = cell.NumericCellValue;
break;
case CellType.String:
datarow[j] = cell.StringCellValue;
break;
case CellType.Blank:
datarow[j] = "";
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
datarow[j] = strFORMULA.ToString();
}
else
{
datarow[j] = null;
}
break;
case CellType.Numeric:
datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
datarow[j] = "";
break;
}
break;
default:
con = false;
break;
}
if (!con)
{
break;
}
}
if (con)
{
datatable.Rows.Add(datarow);
}
}
}
#region 清除最后的空行
for (int i = datatable.Rows.Count - 1; i > 0; i--)
{
bool isnull = true;
for (int j = 0; j < datatable.Columns.Count; j++)
{
if (datatable.Rows[i][j] != null)
{
if (datatable.Rows[i][j].ToString() != "")
{
isnull = false;
break;
}
}
}
if (isnull)
{
datatable.Rows[i].Delete();
}
}
#endregion
return datatable;
}
public ActionResult ExportWipDiscreteJobs(string queryWord, string queryStartDate, string queryEndDate, string queryStartJobDate, string queryEndJobDate, string queryType, string queryPerson)
{
string userName = string.Empty;
if (User.Identity.IsAuthenticated)
{
userName = User.Identity.Name;
}
IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
sheet.SetColumnWidth(0, 20 * 256);
sheet.SetColumnWidth(1, 12 * 256);
sheet.SetColumnWidth(2, 18 * 256);
sheet.SetColumnWidth(3, 60 * 256);
sheet.SetColumnWidth(4, 8 * 256);
sheet.SetColumnWidth(5, 8 * 256);
sheet.SetColumnWidth(6, 30 * 256);
sheet.SetColumnWidth(7, 15 * 256);
int iRow = 0;
IRow titleRow = sheet.CreateRow(iRow);
titleRow.Height = 20 * 20;
titleRow.CreateCell(0).SetCellValue("作业号");
titleRow.CreateCell(1).SetCellValue("作业日期");
titleRow.CreateCell(2).SetCellValue("装配件");
titleRow.CreateCell(3).SetCellValue("装配件描述");
titleRow.CreateCell(4).SetCellValue("需求数");
titleRow.CreateCell(5).SetCellValue("生产线");
titleRow.CreateCell(6).SetCellValue("备注");
titleRow.CreateCell(7).SetCellValue("上线时间");
string userRole = AscmUserInfoService.GetInstance().GetUserRoleName(userName);
string userLogisticsClass = AscmUserInfoService.GetInstance().GetUserLogisticsName(userName);
string whereOther = "", whereQueryWord = "";
if (userRole.IndexOf("物流班长") > -1 || userRole.IndexOf("物流组长") > -1)
{
string ids_userId = AscmAllocateRuleService.GetInstance().GetLogisticsRankerName(userLogisticsClass, userName);
if (!string.IsNullOrEmpty(ids_userId))
whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids_userId, "workerId");
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
else if (userRole.IndexOf("排产员") > -1)
{
whereQueryWord = "workerId = '" + userName + "'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
else if (userRole.IndexOf("领料员") > -1)
{
string ids_userId = AscmAllocateRuleService.GetInstance().GetLogisticsRankerName(userLogisticsClass, userName, true);
if (!string.IsNullOrEmpty(ids_userId))
whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids_userId, "workerId");
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
if (string.IsNullOrEmpty(queryStartDate) && string.IsNullOrEmpty(queryEndDate) && string.IsNullOrEmpty(queryStartJobDate) && string.IsNullOrEmpty(queryEndJobDate))
throw new Exception("请选择导出日期！");
//上传日期
if (!string.IsNullOrEmpty(queryStartDate) && !string.IsNullOrEmpty(queryEndDate))
{
queryStartDate = queryStartDate + " 00:00:00";
queryEndDate = queryEndDate + " 23:59:59";
whereQueryWord = "time >= '" + queryStartDate + "'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
whereQueryWord = "time <= '" + queryEndDate + "'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
else if (!string.IsNullOrEmpty(queryStartDate) && string.IsNullOrEmpty(queryEndDate))
{
whereQueryWord = "time like '" + queryStartDate + "%'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
else if (string.IsNullOrEmpty(queryStartDate) && string.IsNullOrEmpty(queryEndDate))
{
whereQueryWord = "time like '" + queryEndDate + "%'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
//作业日期
if (!string.IsNullOrEmpty(queryStartJobDate) && !string.IsNullOrEmpty(queryEndJobDate))
{
queryStartJobDate = queryStartJobDate + " 00:00:00";
queryEndJobDate = queryEndJobDate + " 23:59:59";
whereQueryWord = "time >= '" + queryStartJobDate + "'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
whereQueryWord = "time <= '" + queryEndJobDate + "'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
else if (!string.IsNullOrEmpty(queryStartJobDate) && string.IsNullOrEmpty(queryEndJobDate))
{
whereQueryWord = "time like '" + queryStartJobDate + "%'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
else if (string.IsNullOrEmpty(queryStartJobDate) && !string.IsNullOrEmpty(queryEndJobDate))
{
whereQueryWord = "time like '" + queryEndJobDate + "%'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryType))
{
whereQueryWord = "identificationId = " + queryType;
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryPerson))
{
whereQueryWord = "workerId = '" + queryPerson + "'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
whereQueryWord = "status > 0";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
try
{
List<AscmDiscreteJobs> listAscmDiscreteJobs = AscmDiscreteJobsService.GetInstance().GetList(null, "", "", queryWord, whereOther, false);
if (listAscmDiscreteJobs != null && listAscmDiscreteJobs.Count > 0)
{
foreach (AscmDiscreteJobs ascmDiscreteJobs in listAscmDiscreteJobs)
{
iRow++;
IRow row = sheet.CreateRow(iRow);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue(ascmDiscreteJobs.jobId);
row.CreateCell(1).SetCellValue(ascmDiscreteJobs.jobDate);
row.CreateCell(2).SetCellValue(ascmDiscreteJobs.jobInfoId);
row.CreateCell(3).SetCellValue(ascmDiscreteJobs.jobDesc);
row.CreateCell(4).SetCellValue(ascmDiscreteJobs.count);
row.CreateCell(5).SetCellValue(ascmDiscreteJobs.lineAndSequence);
row.CreateCell(6).SetCellValue(ascmDiscreteJobs.tip);
row.CreateCell(7).SetCellValue(ascmDiscreteJobs.onlineTime);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("导出失败(Export AscmDiscreteJobs)", ex);
throw ex;
}
byte[] buffer = new byte[] { };
using (System.IO.MemoryStream stream = new MemoryStream())
{
wb.Write(stream);
buffer = stream.GetBuffer();
}
string fileName = DateTime.Now.ToString("yyyyMMhh") + "排产单.xls";
return File(buffer, "application/vnd.ms-excel", fileName);
}
public string readCellExcel(string filePath, string isheetname, int irow, int icolumn)
{
string result = "";
try
{
using (StreamReader input = new StreamReader(filePath))
{
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new NPOI.POIFS.FileSystem.POIFSFileSystem(input.BaseStream));
if (null == workbook)
{
result = "";
}
NPOI.HSSF.UserModel.HSSFFormulaEvaluator formulaEvaluator = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workbook);
NPOI.HSSF.UserModel.HSSFDataFormatter dataFormatter = new NPOI.HSSF.UserModel.HSSFDataFormatter(new CultureInfo("vi-VN"));
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(isheetname);
NPOI.SS.UserModel.IRow row = sheet.GetRow(irow);
if (row != null)
{
short minColIndex = row.FirstCellNum;
short maxColIndex = row.LastCellNum;
if (icolumn >= minColIndex || icolumn <= maxColIndex)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(icolumn);
if (cell != null)
{
result = dataFormatter.FormatCellValue(cell, formulaEvaluator);
}
}
}
}
}
catch (Exception ex)
{
string test = ex.Message;
}
return result;
}
public ActionResult MaterialExport(string queryWord, string queryType, string queryStarDocnumber, string queryEndDocnumber, string zmtlCategoryStatus, string dmtlCategoryStatus, string wmtlCategoryStatus, string queryDescribe)
{
string userName = string.Empty;
if (User.Identity.IsAuthenticated)
{
userName = User.Identity.Name;
}
IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
sheet.SetColumnWidth(0, 20 * 256);
sheet.SetColumnWidth(1, 13 * 256);
sheet.SetColumnWidth(2, 30 * 256);
sheet.SetColumnWidth(3, 15 * 256);
sheet.SetColumnWidth(4, 15 * 256);
sheet.SetColumnWidth(5, 15 * 256);
int iRow = 0;
IRow titleRow = sheet.CreateRow(iRow);
titleRow.Height = 20 * 20;
titleRow.CreateCell(0).SetCellValue("物料编码");
titleRow.CreateCell(1).SetCellValue("供应类型");
titleRow.CreateCell(2).SetCellValue("物料描述");
titleRow.CreateCell(3).SetCellValue("总装备料形式");
titleRow.CreateCell(4).SetCellValue("电装备料形式");
titleRow.CreateCell(5).SetCellValue("其他备料形式");
try
{
string whereOther = "", whereQueryWord = "";
whereQueryWord = "wipSupplyType < 4";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
if (!string.IsNullOrEmpty(queryType))
{
whereQueryWord = "wipSupplyType = " + queryType;
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryStarDocnumber) && !string.IsNullOrEmpty(queryEndDocnumber))
{
if (queryStarDocnumber == queryEndDocnumber)
{
whereQueryWord = "docNumber like '" + queryStarDocnumber + "%'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
else
{
whereQueryWord = "docNumber >= '" + queryStarDocnumber + "'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
whereQueryWord = "docNumber <= '" + queryEndDocnumber + "'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
}
else if (!string.IsNullOrEmpty(queryStarDocnumber) && string.IsNullOrEmpty(queryEndDocnumber))
{
whereQueryWord = "docNumber like '" + queryStarDocnumber + "%'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
else if (string.IsNullOrEmpty(queryStarDocnumber) && !string.IsNullOrEmpty(queryEndDocnumber))
{
whereQueryWord = "docNumber like '" + queryEndDocnumber + "%'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
else
{
whereQueryWord = "docNumber like '20%'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
if (!string.IsNullOrEmpty(zmtlCategoryStatus))
{
if (zmtlCategoryStatus != "qb")
{
if (zmtlCategoryStatus != "kz")
whereQueryWord = "zMtlCategoryStatus = '" + zmtlCategoryStatus + "'";
else
whereQueryWord = "zMtlCategoryStatus is null";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
}
if (!string.IsNullOrEmpty(dmtlCategoryStatus))
{
if (dmtlCategoryStatus != "qb")
{
if (dmtlCategoryStatus != "kz")
whereQueryWord = "dMtlCategoryStatus = '" + dmtlCategoryStatus + "'";
else
whereQueryWord = "dMtlCategoryStatus is null";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
}
if (!string.IsNullOrEmpty(wmtlCategoryStatus))
{
if (wmtlCategoryStatus != "qb")
{
if (wmtlCategoryStatus != "kz")
whereQueryWord = "wMtlCategoryStatus = '" + wmtlCategoryStatus + "'";
else
whereQueryWord = "wMtlCategoryStatus is null";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
}
if (string.IsNullOrEmpty(zmtlCategoryStatus) && string.IsNullOrEmpty(dmtlCategoryStatus) && string.IsNullOrEmpty(wmtlCategoryStatus))
{
whereQueryWord = "isFlag = 0";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
if (!string.IsNullOrEmpty(queryDescribe))
{
whereQueryWord = "description like '%" + queryDescribe + "%'";
whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord);
}
List<AscmMaterialItem> list = AscmMaterialItemService.GetInstance().GetList(null, "", "", "", whereOther, false);
if (list != null && list.Count > 0)
{
foreach (AscmMaterialItem ascmMateiralItem in list)
{
iRow++;
IRow row = sheet.CreateRow(iRow);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue(ascmMateiralItem.docNumber);
row.CreateCell(1).SetCellValue(ascmMateiralItem.wipSupplyTypeCn);
row.CreateCell(2).SetCellValue(ascmMateiralItem.description);
row.CreateCell(3).SetCellValue(ascmMateiralItem._zMtlCategoryStatus);
row.CreateCell(4).SetCellValue(ascmMateiralItem._dMtlCategoryStatus);
row.CreateCell(5).SetCellValue(ascmMateiralItem._wMtlCategoryStatus);
}
}
}
catch (Exception ex)
{
YnBaseClass2.Helper.LogHelper.GetLog().Error("导出失败(Export AscmMaterialItem)", ex);
throw ex;
}
byte[] buffer = new byte[] { };
using (System.IO.MemoryStream stream = new MemoryStream())
{
wb.Write(stream);
buffer = stream.GetBuffer();
}
return File(buffer, "application/vnd.ms-excel", "备料形式维护.xls");
}
// 送货批单明细报表导出
public ActionResult DeliveryBatDetailExport(int? mainId)
{
IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
sheet.SetColumnWidth(0, 16 * 256);
sheet.SetColumnWidth(1, 12 * 256);
sheet.SetColumnWidth(2, 16 * 256);
sheet.SetColumnWidth(3, 30 * 256);
sheet.SetColumnWidth(4, 17 * 256);
sheet.SetColumnWidth(5, 12 * 256);
sheet.SetColumnWidth(6, 12 * 256);
sheet.SetColumnWidth(7, 10 * 256);
sheet.SetColumnWidth(8, 12 * 256);
sheet.SetColumnWidth(9, 10 * 256);
sheet.SetColumnWidth(10, 12 * 256);
IRow titleRow = sheet.CreateRow(0);
titleRow.Height = 20 * 20;
titleRow.CreateCell(0).SetCellValue("批送货单号");
titleRow.CreateCell(1).SetCellValue("批条码号");
titleRow.CreateCell(2).SetCellValue("物料编码");
titleRow.CreateCell(3).SetCellValue("物料描述");
titleRow.CreateCell(4).SetCellValue("生成日期");
titleRow.CreateCell(5).SetCellValue("出货子库");
titleRow.CreateCell(6).SetCellValue("收货子库");
titleRow.CreateCell(7).SetCellValue("状态");
titleRow.CreateCell(8).SetCellValue("送货地点");
titleRow.CreateCell(9).SetCellValue("总数量");
titleRow.CreateCell(10).SetCellValue("容器绑定数量");
string fileDownloadName = "送货批单明细报表";
if (mainId.HasValue)
{
List<AscmDeliBatSumDetail> list = AscmDeliBatSumDetailService.GetInstance().GetListByMainId(mainId.Value);
if (list != null)
{
int rowIndex = 0;
foreach (AscmDeliBatSumDetail deliBatSumDetail in list)
{
IRow row = sheet.CreateRow(++rowIndex);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue(deliBatSumDetail.batchDocNumber);
row.CreateCell(1).SetCellValue(deliBatSumDetail.batchBarCode);
row.CreateCell(2).SetCellValue(deliBatSumDetail.materialDocNumber);
row.CreateCell(3).SetCellValue(deliBatSumDetail.materialDescription);
row.CreateCell(4).SetCellValue(deliBatSumDetail.batchCreateTime);
row.CreateCell(5).SetCellValue(deliBatSumDetail.batchSupperWarehouse);
row.CreateCell(6).SetCellValue(deliBatSumDetail.batchWarehouseId);
row.CreateCell(7).SetCellValue(deliBatSumDetail.batchStatusCn);
row.CreateCell(8).SetCellValue(deliBatSumDetail.batchWipLine);
row.CreateCell(9).SetCellValue(deliBatSumDetail.totalNumber.ToString());
row.CreateCell(10).SetCellValue(deliBatSumDetail.containerBindNumber.ToString());
}
}
}
byte[] buffer = new byte[] { };
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
wb.Write(stream);
buffer = stream.GetBuffer();
}
return File(buffer, "application/vnd.ms-excel", fileDownloadName + ".xls");
}
private void Relatorio_GerarArquivoProcesso(System.Data.DataSet dados, String path, String fileName, String template)
{
#region Declaração de variáveis
Boolean sucesso = false;
System.IO.FileStream file = null;
System.IO.FileStream fileTemplate = null;
NPOI.HSSF.UserModel.HSSFWorkbook xlPackage = null;
#endregion
try
{
#region Inicialização de objetos
fileTemplate = new System.IO.FileStream(path + template, System.IO.FileMode.Open, System.IO.FileAccess.Read);
#endregion
#region Gera o arquivo do relatório
xlPackage = new NPOI.HSSF.UserModel.HSSFWorkbook(fileTemplate);
this.Relatorio_PreencherDados(xlPackage, dados.Tables[0], "Processos");
file = new System.IO.FileStream(path + fileName, System.IO.FileMode.Create);
xlPackage.Write(file);
file.Close();
#endregion
}
catch (Exception ex)
{
#region Tratamento de erro
throw new Exception("MMAA.Pedidos.Web.WS.Pedido.Relatorio_GerarArquivoProcesso: " + ex.Message);
#endregion
}
}
public bool OpenExcel()
{
bool isSuccess = false;
try
{
FileStream fileStream = new FileStream(excelpath, FileMode.Open, FileAccess.ReadWrite);
excelWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream);
isSuccess = true;
}
catch (Exception)
{
}
return isSuccess;
}
public ActionResult ExportMsg()
{
List<String> list = new List<String>();
List<String> listName = new List<String>();
#region 初始化数据
//得到选中项 这是比较笨的方法，有机会要改进哈
if (Request.Form["StuNum"] != null) { list.Add("StuNum"); listName.Add("学号"); }
if (Request.Form["StuName"] != null) { list.Add("StuName"); listName.Add("姓名"); }
if (Request.Form["Gender"] != null) { list.Add("Gender"); listName.Add("性别"); }
if (Request.Form["QQNum"] != null) { list.Add("QQNum"); listName.Add("QQ号"); }
if (Request.Form["Email"] != null) { list.Add("Email"); listName.Add("电子邮箱"); }
if (Request.Form["Birthday"] != null) { list.Add("Birthday"); listName.Add("生日"); }
if (Request.Form["Class"] != null) { list.Add("Class"); listName.Add("班级"); }
if (Request.Form["TelephoneNumber"] != null) { list.Add("TelephoneNumber"); listName.Add("电话号码"); }
if (Request.Form["Department"] != null) { list.Add("Department"); listName.Add("部门"); }
if (Request.Form["StudyGuideNumber"] != null) { list.Add("StudyGuideNumber"); listName.Add("指导学长学姐"); }
if (Request.Form["JoinTime"] != null) { list.Add("JoinTime"); listName.Add("加入时间"); }
if (Request.Form["Major"] != null) { list.Add("Major"); listName.Add("主修"); }
if (Request.Form["Counseloer"] != null) { list.Add("Counselor"); listName.Add("辅导员"); }
if (Request.Form["HeadTeacher"] != null) { list.Add("HeadTeacher"); listName.Add("班主任"); }
if (Request.Form["UndergraduateTutor"] != null) { list.Add("UndergraduateTutor"); listName.Add("毕业导师"); }
if (Request.Form["HomPhoneNumber"] != null) { list.Add("HomPhoneNumber"); listName.Add("家庭电话"); }
if (Request.Form["FamilyAddress"] != null) { list.Add("FamilyAddress"); listName.Add("家庭地址"); }
if (Request.Form["Sign"] != null) { list.Add("Sign"); listName.Add("个性签名"); }
int depart=0;
if (!string.IsNullOrEmpty((Request.Form["depart"])))
{
depart = Convert.ToInt32(Request.Form["depart"]);
}
if (list.Count == 0)
{
return Content("<script>alert('您没有选择任何项喔~~！');window.location='/PersonalManger/CheckMember/index'</script>");
}
DataTable dt = OperateContext.Current.BLLSession.IMemberInformationBLL.GetPartData(list,depart);
//如果选择了部门那么导出时要将数字转为部门名
int count = 0;
//如果选择的指导学长学姐要将学号转换为姓名,下面变量是的得到导出的学号在list列表里的顺序
int guideCount=0;
if (Request.Form["Department"] != null)
{
for (int i = 0; i < list.Count; i++)
{
if (list[i] == "Department") { count = i; }
}
}
if (Request.Form["StudyGuideNumber"] != null)
{
for (int i = 0; i<list.Count; i++)
{
if (list[i] == "StudyGuideNumber") { guideCount = i; }
}
}
#endregion
NPOI.HSSF.UserModel.HSSFWorkbook workbook= new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet sheet = workbook.CreateSheet("第一页") as NPOI.HSSF.UserModel.HSSFSheet;
NPOI.HSSF.UserModel.HSSFRow hr = sheet.CreateRow(0) as NPOI.HSSF.UserModel.HSSFRow;
for(int i=0;i<listName.Count;i++)
{
hr.CreateCell(i).SetCellValue(listName[i]);
}
for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
NPOI.HSSF.UserModel.HSSFRow hro = sheet.CreateRow(rowIndex + 1) as NPOI.HSSF.UserModel.HSSFRow;
for (int colIndex = 0; colIndex < listName.Count; colIndex++)
{
string str="";
if (colIndex == count&&count!=0)
{
if (dt.Rows[rowIndex][colIndex].ToString() == "10001") { str = "NET应用开发部"; }
if (dt.Rows[rowIndex][colIndex].ToString() == "10002") { str = "安卓应用开发部"; }
if (dt.Rows[rowIndex][colIndex].ToString() == "10003") { str = "硬件编程技术部"; }
if (dt.Rows[rowIndex][colIndex].ToString() == "10004") { str = "系统编程技术部"; }
if (dt.Rows[rowIndex][colIndex].ToString() == "10007") { str = "暂未加入部门"; }
hro.CreateCell(colIndex).SetCellValue(str);
}
else
{
if (colIndex == guideCount && guideCount != 0)
{
string num = dt.Rows[rowIndex][colIndex].ToString();
if (!string.IsNullOrEmpty(num))
{
string name = OperateContext.Current.BLLSession.IMemberInformationBLL.GetListBy(u => u.StuNum == num).FirstOrDefault().StuName;
hro.CreateCell(colIndex).SetCellValue(name);
}
}
else
{
hro.CreateCell(colIndex).SetCellValue(dt.Rows[rowIndex][colIndex].ToString());
}
}
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel");
}
