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成功";
}
}
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 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 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"));
}
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);
}
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"));
}
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 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 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 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;
}
}
}
/// <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;
}
}
}
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 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")));
}
}
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 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"));
}
/// <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 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();
}
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 匯出客戶銀行資訊()
{
//建立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"));
}
/*
* 作用：将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);
}
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);
}
}
}
/// <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 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 void getExcel(DataTable dt)
{
string path = ConfigHelper.GetConfigString("AttachmentPath");
if (path.StartsWith("~"))
{
path = Server.MapPath(path);
}
if (!path.EndsWith("\\"))
{
path = path + "\\";
}
path += "ImportExcelSVM\\Download\\";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string name = "";
if (MCSTabControl1.SelectedIndex == 0)
{
name = "零售商导购协议导出";
}
else
{
name = "零售商返利协议导出";
}
path += name;
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet(name);
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++)
{
IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
//写入到客户端
FileStream file = new FileStream(path, FileMode.Create);
book.Write(file);
file.Close();
book = null;
sheet = null;
Downloadfile(path, "Export-" + name + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xls");
}
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 AgreementDetailListExportExcel(long accountId, int enumOrderTypeId, DateTime?startDate, DateTime?endDate)
{
var queryModel = new AccountQuery()
{
StartDate = startDate,
EndDate = endDate.HasValue ? endDate.Value.AddDays(1) : endDate,
AccountId = accountId,
PageSize = int.MaxValue,
PageNo = 1
};
QueryPageModel <DTO.AccountMetaModel> pageModelMetaInfo = AccountApplication.GetAccountMeta(queryModel);
var mode = pageModelMetaInfo.Models.ToList().Select(e => new DTO.AccountMetaModel
{
AccountId = e.Id,
Id = e.Id,
EndDate = e.EndDate,
StartDate = e.StartDate,
MetaKey = e.MetaKey,
MetaValue = e.MetaValue,
DateRange = e.StartDate.ToString("yyyy-MM-dd") + " 至 " + e.EndDate.ToString("yyyy-MM-dd")
}).ToList();
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("类型");
row1.CreateCell(1).SetCellValue("营销类型");
row1.CreateCell(2).SetCellValue("费用");
row1.CreateCell(3).SetCellValue("服务周期");
sheet1.SetColumnWidth(0, 550 * 5);
sheet1.SetColumnWidth(1, 550 * 20);
sheet1.SetColumnWidth(2, 550 * 8);
sheet1.SetColumnWidth(3, 550 * 15);
//将数据逐步写入sheet1各个行
for (int i = 0; i < mode.Count(); i++)
{
IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue("营销服务费");
rowtemp.CreateCell(1).SetCellValue(mode[i].MetaKey);
rowtemp.CreateCell(2).SetCellValue(mode[i].MetaValue);
rowtemp.CreateCell(3).SetCellValue(mode[i].DateRange);
}
// 写入到客户端
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 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));
}
internal static NPOI.SS.UserModel.IWorkbook CreateWorkbookFromMultipleDictionaries(string sheetName, List <string> keys, Dictionary <string, Dictionary <string, string> > languages)
{
if (string.IsNullOrEmpty(sheetName))
{
throw new ArgumentNullException("sheetName");
}
if (keys == null)
{
throw new ArgumentNullException("keys");
}
if (languages == null)
{
throw new ArgumentNullException("languages");
}
keys.Sort();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
var worksheet = workbook.CreateSheet(sheetName);
int column = 0;
int row = 0;
var firstRow = worksheet.CreateRow(row++);
var firstCell = firstRow.CreateCell(column);
firstCell.SetCellValue(string.Empty);
foreach (string key in keys)
{
var currentRow = worksheet.CreateRow(row++);
var currentCell = currentRow.CreateCell(column);
currentCell.SetCellValue(key);
}
column++;
foreach (var pair in languages)
{
row = 0;
var currentRow = worksheet.GetRow(row++);
var currentCell = currentRow.CreateCell(column);
currentCell.SetCellValue(pair.Key);
foreach (string key in keys)
{
if (pair.Value.ContainsKey(key))
{
currentRow = worksheet.GetRow(row++);
currentCell = currentRow.CreateCell(column);
currentCell.SetCellValue(pair.Value[key]);
}
else
{
currentRow = worksheet.GetRow(row++);
currentCell = currentRow.CreateCell(column);
currentCell.SetCellValue(string.Empty);
}
}
column++;
}
return(workbook);
}
protected static MemoryStream GetMS(List <FishEntity.CallRecordsEntity> 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.CallRecordsEntity model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.code);
CreateCell(row, 1, model.customer);
CreateCell(row, 2, model.linkman);
CreateCell(row, 3, model.mobile);
CreateCell(row, 4, model.telephone);
CreateCell(row, 5, model.customerlevel);
CreateCell(row, 6, model.currentdate.Value.ToString("yyyy-MM-dd"));
CreateCell(row, 7, model.communicatecontent);
CreateCell(row, 8, model.requiredquantity);
CreateCell(row, 9, model.products);
CreateCell(row, 10, model.weekestimate);
CreateCell(row, 11, model.monthestimate);
CreateCell(row, 12, model.address);
# endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
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 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");
}
// 送货合单查询报表导出
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");
}
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成功";
}
}
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 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");
}
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 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 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 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 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(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 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 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");
}
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 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;
}