public FileResult OmitCheckCountExportExcel()
{
PatrolRouteStat_SW sw = new PatrolRouteStat_SW();
sw.orgNo = Request.Params["BYORGNO"];
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUReportCls.getPatrolRouteStatModel(sw);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 30 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
sheet1.SetColumnWidth(2, 10 * 256);
sheet1.SetColumnWidth(3, 10 * 256);
sheet1.SetColumnWidth(4, 10 * 256);
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("未巡统计表");
row.GetCell(0).CellStyle = getCellStyleTitle(book);
row = sheet1.CreateRow(1);
row.CreateCell(0).SetCellValue("单位/姓名");
row.CreateCell(1).SetCellValue("应巡");
row.CreateCell(2).SetCellValue("已巡");
row.CreateCell(3).SetCellValue("未巡");
row.CreateCell(4).SetCellValue("完成率");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(3).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4));
int rowI = 0;
foreach (var v in list)
{
row = sheet1.CreateRow(rowI + 2);
row.CreateCell(0).SetCellValue(v.ORGName);
row.CreateCell(1).SetCellValue(v.PointCount);
row.CreateCell(2).SetCellValue(v.PointCount0);
row.CreateCell(3).SetCellValue(v.PointCount1);
row.CreateCell(4).SetCellValue(v.PointCount2);
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = "未巡统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public void NpoiExcel(DataTable dt, string title)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int I = 0; I <= dt.Rows.Count - 1; I++)
{
HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
if (j < 2)
{
string DgvValue = dt.Rows[I][j].ToString();
row2.CreateCell(j).SetCellValue(DgvValue);
sheet.SetColumnWidth(j, 20 * 150);
}
else
{
if (dt.Rows[I][j].ToString().Trim() != "")
{
double DgvValue = Convert.ToDouble(dt.Rows[I][j].ToString());
row2.CreateCell(j).SetCellValue(DgvValue);
sheet.SetColumnWidth(j, 20 * 150);
}
else
{
string DgvValue = dt.Rows[I][j].ToString();
row2.CreateCell(j).SetCellValue(DgvValue);
sheet.SetColumnWidth(j, 20 * 150);
}
}
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
public void NpoiExcel(DataTable dt, string title, string s)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
ICellStyle style = book.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
string[] ss = s.Split(',');
for (int i = 0; i < ss.Length; i++)
{
ICell cell = headerrow.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(ss[i]);
}
for (int I = 0; I <= dt.Rows.Count - 1; I++)
{
HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1);
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
//double a = 0;
//string b = "";
//try
//{
// a = Convert.ToDouble(dt.Rows[I][j].ToString());
// row2.CreateCell(j).SetCellValue(a);
// sheet.SetColumnWidth(j, 20 * 150);
//}
//catch
//{
// b = dt.Rows[I][j].ToString();
// row2.CreateCell(j).SetCellValue(b);
// sheet.SetColumnWidth(j, 20 * 150);
//}
string DgvValue = dt.Rows[I][j].ToString();
if (j == 8 || j == 9)
{
row2.CreateCell(j).SetCellValue(Convert.ToDouble(DgvValue));
}
else
{
row2.CreateCell(j).SetCellValue(DgvValue);
}
sheet.SetColumnWidth(j, 20 * 150);
}
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
Response.BinaryWrite(ms.ToArray());
Response.End();
book = null;
ms.Close();
ms.Dispose();
}
static void Main(string[] args)
{
InitializeWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
//set the width of columns
sheet1.SetColumnWidth(0, 50 * 256);
sheet1.SetColumnWidth(1, 100 * 256);
sheet1.SetColumnWidth(2, 150 * 256);
//set the width of height
sheet1.CreateRow(0).Height = 100 * 20;
sheet1.CreateRow(1).Height = 200 * 20;
sheet1.CreateRow(2).Height = 300 * 20;
sheet1.DefaultRowHeightInPoints = 50;
WriteToFile();
}
public void Export(string fileName, DataGridView dgv)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
IWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "微软雅黑";
cellStyle.SetFont(font);
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("员工信息表");
sheet.SetColumnWidth(0, 50 * 256);
sheet.SetColumnWidth(3, 25 * 256);
int rowCount = dgv.Rows.Count;
int colCount = dgv.Columns.Count;
IRow row = sheet.CreateRow(0);
ICell coloun0 = row.CreateCell(0);
ICell coloun1 = row.CreateCell(1);
ICell coloun2 = row.CreateCell(2);
ICell coloun3 = row.CreateCell(3);
ICell coloun4 = row.CreateCell(4);
ICell coloun5 = row.CreateCell(5);
coloun0.SetCellValue("编号");
coloun0.CellStyle = cellStyle;
coloun1.SetCellValue("工号");
coloun1.CellStyle = cellStyle;
coloun2.SetCellValue("姓名");
coloun2.CellStyle = cellStyle;
coloun3.SetCellValue("入职时间");
coloun3.CellStyle = cellStyle;
coloun4.SetCellValue("民族");
coloun4.CellStyle = cellStyle;
coloun5.SetCellValue("籍贯");
coloun5.CellStyle = cellStyle;
for (int i = 0; i < rowCount; i++)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow((sheet.LastRowNum + 1));
for (int j = 0; j < colCount; j++)
{
if (dgv.Columns[j].Visible && dgv.Rows[i].Cells[j].Value != null)
{
NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(j);
cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString());
cell.CellStyle = cellStyle;
}
}
}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK);
}
private static MemoryStream GetExcelMemoryStream(List <dynamic> orderItemList)
{
var first = orderItemList.FirstOrDefault();
//创建表格
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
#region 定义样式
//创建字体
var cellStyleFont = (HSSFFont)book.CreateFont();
cellStyleFont.IsBold = true;
cellStyleFont.FontName = "宋体";
cellStyleFont.FontHeightInPoints = 10;
//RGB自定义背景色
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)220, (byte)220, (byte)220);
// titleStyle
HSSFCellStyle titleStyle = (HSSFCellStyle)book.CreateCellStyle();
//（自定义背景色）单元格背景颜色 和FillPattern必须一起设置
titleStyle.FillForegroundColor = HSSFColor.Pink.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
titleStyle.BorderBottom = BorderStyle.Thin;
titleStyle.BorderLeft = BorderStyle.Thin;
titleStyle.BorderRight = BorderStyle.Thin;
titleStyle.BorderTop = BorderStyle.Thin;
titleStyle.SetFont(cellStyleFont);//设置字体
//headStyle
HSSFCellStyle headStyle = (HSSFCellStyle)book.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; //水平居中
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.SetFont(cellStyleFont);
//borderStyle
HSSFCellStyle borderStyle = (HSSFCellStyle)book.CreateCellStyle();
borderStyle.BorderBottom = BorderStyle.Thin;
borderStyle.BorderLeft = BorderStyle.Thin;
borderStyle.BorderRight = BorderStyle.Thin;
borderStyle.BorderTop = BorderStyle.Thin;
#endregion
#region 表头
//设置单元格宽度
sheet.SetColumnWidth(0, 5000); //采购日期
sheet.SetColumnWidth(1, 5000); //门店名称
sheet.SetColumnWidth(2, 14000); //门店地址
sheet.SetColumnWidth(3, 4500); //采购数量
sheet.SetColumnWidth(4, 4500); //操作人
//表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(4);
row.Height = 350;
row.CreateCell(0).SetCellValue("采购日期");
row.CreateCell(1).SetCellValue("门店名称");
row.CreateCell(2).SetCellValue("门店地址");
row.CreateCell(3).SetCellValue("采购数量");
row.CreateCell(4).SetCellValue("操作人");
for (int i = 0; i < 5; i++)
{
row.Cells[i].CellStyle = titleStyle;
}
//合并单元格
CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 4);
sheet.AddMergedRegion(region0);
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("冷链食品处理详情");
row0.Cells[0].CellStyle = headStyle;
var row0Cell4 = row0.CreateCell(4);
row0Cell4.CellStyle = headStyle;
IRow row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue("处理商品批次码");
row1.CreateCell(1).SetCellValue(first.BatchNo.ToString());
row1.Cells[0].CellStyle = titleStyle;
IRow row2 = sheet.CreateRow(2);
row2.CreateCell(0).SetCellValue("处理日期");
row2.CreateCell(1).SetCellValue(first.NoticeTime?.ToString("yyyy.MM.dd"));
row2.Cells[0].CellStyle = titleStyle;
IRow row3 = sheet.CreateRow(3);
row3.CreateCell(0).SetCellValue("处理人");
row3.CreateCell(1).SetCellValue(first.NoticeUserName.ToString());
row3.Cells[0].CellStyle = titleStyle;
//设置黑色边框
for (int i = 1; i < 4; i++)
{
var iRow = sheet.GetRow(i);
for (int j = 1; j < 5; j++)
{
var jCell = (j == 1) ? iRow.GetCell(j) : iRow.CreateCell(j);
jCell.CellStyle = borderStyle;
}
}
#endregion
#region 循环数据
int index = 5;
foreach (var item in orderItemList)
{
IRow itemRow = sheet.CreateRow(index);
var rowCell0 = itemRow.CreateCell(0);
rowCell0.SetCellValue((item.ImportTime ?? item.SubTime).ToString("yyyy.MM.dd"));
rowCell0.CellStyle = borderStyle;
var rowCell1 = itemRow.CreateCell(1);
rowCell1.SetCellValue(item.TargetStoreName.ToString());
rowCell1.CellStyle = borderStyle;
var rowCell2 = itemRow.CreateCell(2);
rowCell2.SetCellValue(item.TargetStoreAddress.ToString());
rowCell2.CellStyle = borderStyle;
var rowCell3 = itemRow.CreateCell(3);
rowCell3.SetCellValue(item.Number?.ToString());
rowCell3.CellStyle = borderStyle;
var rowCell4 = itemRow.CreateCell(4);
rowCell4.SetCellValue(item.ImportUserNames.ToString());
rowCell4.CellStyle = borderStyle;
index++;
}
#endregion
MemoryStream ms = new MemoryStream();
book.Write(ms);
return(ms);
}
/// <summary>
/// 导出
/// </summary>
/// <returns></returns>
public FileResult OutRaiLExportExcel()
{
OutRaiLCount_SW sw = new OutRaiLCount_SW();
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUCheckCls.getOutRaiLCountModel(sw);
DateTime dt1 = Convert.ToDateTime(sw.DateBegin);
DateTime dt2 = Convert.ToDateTime(sw.DateEnd);
int days = ClsStr.getDateDiff(sw.DateBegin, sw.DateEnd) + 1;//日期包含天数
//vMenu.MENUNAME 页面/菜单名称
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 30 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
for (int i = 0; i < days; i++)
{
sheet1.SetColumnWidth(i + 2, 10 * 256);
}
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("出围统计表");
row.GetCell(0).CellStyle = getCellStyleTitle(book);
row = sheet1.CreateRow(1);
row.CreateCell(0).SetCellValue("单位/姓名");
row.CreateCell(1).SetCellValue("出围合计");
row.CreateCell(2).SetCellValue("日期（日）");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row = sheet1.CreateRow(2);
for (int i = 0; i < days; i++)
{
DateTime tm = dt1.AddDays(i);
row.CreateCell(i + 2).SetCellValue(tm.ToString("dd"));
row.GetCell(i + 2).CellStyle = getCellStyleHead(book);
}
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, days + 1));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 2, days - 2));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 1));
int rowI = 0;
foreach (var v in list)
{
row = sheet1.CreateRow(rowI + 3);
row.CreateCell(0).SetCellValue(v.ORGName);
row.CreateCell(1).SetCellValue(v.Count);
string[] arr = v.DayCountList.Split(',');
for (int i = 0; i < days; i++)
{
row.CreateCell(i + 2).SetCellValue(arr[i]);
}
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = "出围统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
