/// <summary>
/// Excel导出返回文件路径
/// </summary>
/// <param name="fileName"></param>
/// <param name="title"></param>
/// <param name="sheetName"></param>
/// <param name="dt"></param>
/// <param name="FilePath"></param>
/// <param name="ext"></param>
/// <returns></returns>
public static string ExcelFilePath(string[] fileName, string[] title, string sheetName, DataTable dt, string FilePath, string ext)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < title.Length; i++)
{
row.CreateCell(i).SetCellValue(title[i]);
}
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < fileName.Length; j++)
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
}
}
}
if (!Directory.Exists(HttpContext.Current.Server.MapPath(FilePath)))
{
Directory.CreateDirectory(HttpContext.Current.Server.MapPath(FilePath));
}
FilePath += "/" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000) + Path.GetExtension(ext);
FileStream fs2 = File.Create(HttpContext.Current.Server.MapPath(FilePath));
book.Write(fs2);
fs2.Close();
return(FilePath);
//写入到客户端
}
private static void createColumnName(NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ISheet sheet)
{
SetCellRangeAddress(sheet, 0, 0, 0, 1);
SetCellRangeAddress(sheet, 0, 0, 3, 4);
SetCellRangeAddress(sheet, 0, 0, 6, 7);
SetCellRangeAddress(sheet, 0, 0, 9, 10);
SetCellRangeAddress(sheet, 0, 0, 12, 14);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("输入功率标定");
row.CreateCell(3).SetCellValue("输出功率标定");
row.CreateCell(6).SetCellValue("反射功率标定");
row.CreateCell(9).SetCellValue("ALC功率标定");
row.CreateCell(12).SetCellValue("衰减补偿");
row.GetCell(0).CellStyle = GetCellStyle(book);
row.GetCell(3).CellStyle = GetCellStyle(book);
row.GetCell(6).CellStyle = GetCellStyle(book);
row.GetCell(9).CellStyle = GetCellStyle(book);
row.GetCell(12).CellStyle = GetCellStyle(book);
row = sheet.CreateRow(1);
for (int i = 0; i < 4; i++)
{
row.CreateCell(3 * i, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("采样电压");
row.CreateCell(3 * i + 1, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("定标点");
row.GetCell(3 * i).CellStyle = GetCellStyle(book);
row.GetCell(3 * i + 1).CellStyle = GetCellStyle(book);
}
row.CreateCell(12, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("起始值");
row.CreateCell(13, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("结束值");
row.CreateCell(14, NPOI.SS.UserModel.CellType.Numeric).SetCellValue("补偿值");
row.GetCell(12).CellStyle = GetCellStyle(book);
row.GetCell(13).CellStyle = GetCellStyle(book);
row.GetCell(14).CellStyle = GetCellStyle(book);
}
public static HSSFWorkbook CreateExecl(List <string> title, List <AppUserPerson> 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.RowStyle.FillBackgroundColor = "";
int i = 0;
foreach (var item in title)
{
row1.CreateCell(i).SetCellValue(item);
i++;
}
//将数据逐步写入sheet1各个行
for (int j = 0; j < list.Count; j++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(j + 1);
rowtemp.CreateCell(0).SetCellValue(list[j].adnm);
rowtemp.CreateCell(1).SetCellValue(list[j].userName);
rowtemp.CreateCell(2).SetCellValue(list[j].phone);
}
return(book);
}
public static MemoryStream ExportExcel(DataTable dt)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//row1.RowStyle.FillBackgroundColor = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
public void TestEvaluateFormulaWithRowBeyond32768_Bug44539()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
cell.CellFormula = ("SUM(A32769:A32770)");
// put some values in the cells to make the evaluation more interesting
sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31);
sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11);
//HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
NPOI.SS.UserModel.CellValue result;
try
{
result = fe.Evaluate(cell);
}
catch (Exception e)
{
if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!"))
{
Assert.Fail("Identifed bug 44539");
}
throw;
}
Assert.AreEqual(NPOI.SS.UserModel.CellType.Numeric, result.CellType);
Assert.AreEqual(42.0, result.NumberValue, 0.0);
}
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();
}
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.Caption);
index++;
}
// 添加数据
for (int i = 0; i < dt.Rows.Count; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
}
public static Stream RenderDataTableToExcel(string[] headers, DataTable SourceTable)
{
MemoryStream ms = new MemoryStream();
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < headers.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(headers[i]);
}
int rowIndex = 1;
foreach (DataRow row in SourceTable.Rows)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.CreateCell(0).SetCellValue(row["ItemName"].ToString());
dataRow.CreateCell(1).SetCellValue(row["Count"].ToString());
dataRow.CreateCell(2).SetCellValue(row["Percent"].ToString());
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
public byte[] GetExcel(AddmissionContext _context)
{
XSSFWorkbook wb = new XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("录取学生表");
var students = _context.Admissions.Include(a => a.AcceptedStudent).Where(a => a.Uname == userName).ToList();
NPOI.SS.UserModel.IRow row;
row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("考号");
row.CreateCell(1).SetCellValue("姓名");
row.CreateCell(2).SetCellValue("总分");
row.CreateCell(3).SetCellValue("毕业学校");
row.CreateCell(4).SetCellValue("录取方式");
int count = 1;//行号游标
foreach (var student in students)
{
row = sheet.CreateRow(count);
row.CreateCell(0).SetCellValue(student.Sid);
row.CreateCell(1).SetCellValue(student.AcceptedStudent.Sname);
row.CreateCell(2).SetCellValue(student.AcceptedStudent.TotalGrade);
row.CreateCell(3).SetCellValue(student.AcceptedStudent.GraduateSchool);
row.CreateCell(4).SetCellValue(student.AdmissionMethod);
count++;
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
wb.Write(ms);
return(ms.ToArray());
}
/**
* All multi-binomial operator Tests use this to Create a worksheet with a
* huge set of x operator y formulas. Next we call BinomialVerify and verify
* that they are all how we expect.
*/
private static void BinomialOperator(String operator1)
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = (1 + operator1 + 1);
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.CreateRow(x);
for (int y = 1; y < 256 && y > 0; y++)
{
c = r.CreateCell(y);
c.CellFormula = ("" + x + operator1 + y);
}
}
//make sure we do the maximum value of the Int operator
if (s.LastRowNum < short.MaxValue)
{
r = s.GetRow(0);
c = r.CreateCell(0);
c.CellFormula = ("" + short.MaxValue + operator1 + short.MaxValue);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
BinomialVerify(operator1, wb);
}
/// <summary>
/// 服务商结算订单导出
/// </summary>
/// <param name="fileName"></param>
/// <param name="title"></param>
/// <param name="sheetName"></param>
/// <param name="dt"></param>
public static System.IO.MemoryStream ExcelSettlementOrder(string[] fileName, string[] title, string sheetName, DataTable dt)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < title.Length; i++)
{
row.CreateCell(i).SetCellValue(title[i]);
}
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < fileName.Length; j++)
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][fileName[j]].ToString());
}
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
//ms.Seek(0, SeekOrigin.Begin);
ms.Flush();
ms.Position = 0;//流位置归零
return(ms);
//写入到客户端
}
private static void floatTest(String operator1)
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = ("" + float.MinValue + operator1 + float.MinValue);
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.CreateRow(x);
for (int y = 1; y < 256 && y > 0; y = (short)(y + 2))
{
c = r.CreateCell(y);
c.CellFormula = ("" + x + "." + y + operator1 + y + "." + x);
}
}
if (s.LastRowNum < short.MaxValue)
{
r = s.CreateRow(0);
c = r.CreateCell(0);
c.CellFormula = ("" + float.MaxValue + operator1 + float.MaxValue);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
floatVerify(operator1, wb);
}
public void TestDateFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet("TestSheet1");
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0);
NPOI.SS.UserModel.ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"));
c.SetCellValue(new DateTime());
c.CellStyle = (cellStyle);
// Assert.AreEqual("Checking hour = " + hour, date.GetTime().GetTime(),
// NPOI.SS.UserModel.DateUtil.GetJavaDate(excelDate).GetTime());
for (int k = 1; k < 100; k++)
{
r = s.CreateRow(k);
c = r.CreateCell(0);
c.CellFormula = ("A" + (k) + "+1");
c.CellStyle = cellStyle;
}
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
/// <summary>
/// 批量导出需要导出的列表
/// </summary>
/// <returns></returns>
public static MemoryStream GetMemoryStream(DataTable source, List <string> caption = null)
{
//创建Excel文件的对象
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);
if (caption == null)
{
caption = source.Columns.Cast <DataColumn>().ToList().Select(item => item.Caption).ToList();
}
for (int i = 0; i < caption.Count; i++)
{
var element = caption[i];
row1.CreateCell(i).SetCellValue(element);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < source.Rows.Count; i++)
{
var dataRow = source.Rows[i];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < caption.Count; j++)
{
var keyValue = caption[j];
rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
//return File(ms, "application/vnd.ms-excel", fileName);
}
public void TestDataStyle()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
IRow row = s.CreateRow((short)0);
// with Date:
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
row.RowStyle = (cs);
row.CreateCell(0);
// with Calendar:
row = s.CreateRow((short)1);
cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
row.RowStyle = (cs);
row.CreateCell(0);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(1, s.LastRowNum, "LAST ROW ");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
}
/// <summary>
/// 批量导出本校第一批派位学生
/// </summary>
/// <returns></returns>
public FileResult ExportStu2()
{
// schoolname = "401";
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
List <Staff> listRainInfo = db.Staffs.ToList();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("电脑号");
row1.CreateCell(1).SetCellValue("姓名");
//将数据逐步写入sheet1各个行
for (int i = 0; i < listRainInfo.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].StaffNumber.ToString());
rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].Name.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", "第一批电脑派位生名册.xls"));
}
public 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++)
{
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 static void ExcelOut(DataTable dt, string fullXlsFilePath)
{
//创建Excel文件的对象
NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow headRow = sheet1.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
headRow.CreateCell(i).SetCellValue(dt.Columns[i].ToString());
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
//加上设置大小下载下来的.xlsx文件打开时才没有错误
FileStream fileStream = new FileStream(fullXlsFilePath, FileMode.Create, FileAccess.Write);
book.Write(fileStream);//调用这个后会关于文件流，在HSSFWorkbook不会关闭所以在处理时应注意
FileStream fs = new FileStream(fullXlsFilePath, FileMode.Open, FileAccess.Read);
long fileSize = fs.Length;
//加上设置大小下载下来的.xlsx文件打开时才没有错误
HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
byte[] fileBuffer = new byte[fileSize]; fs.Read(fileBuffer, 0, (int)fileSize); HttpContext.Current.Response.BinaryWrite(fileBuffer); fs.Close();
}
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 void TestSheetFunctions()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet("A");
IRow r = null;
ICell c = null;
r = s.CreateRow(0);
c = r.CreateCell(0); c.SetCellValue(1);
c = r.CreateCell(1); c.SetCellValue(2);
s = wb.CreateSheet("B");
r = s.CreateRow(0);
c = r.CreateCell(0); c.CellFormula = ("AVERAGE(A!A1:B1)");
c = r.CreateCell(1); c.CellFormula = ("A!A1+A!B1");
c = r.CreateCell(2); c.CellFormula = ("A!$A$1+A!$B1");
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheet("B");
r = s.GetRow(0);
c = r.GetCell(0);
Assert.IsTrue(("AVERAGE(A!A1:B1)").Equals(c.CellFormula), "expected: AVERAGE(A!A1:B1) got: " + c.CellFormula);
c = r.GetCell(1);
Assert.IsTrue(("A!A1+A!B1").Equals(c.CellFormula), "expected: A!A1+A!B1 got: " + c.CellFormula);
}
/// <summary>
/// 批量导出需要导出的列表
/// </summary>
/// <returns></returns>
public static MemoryStream ExportMemory(DataTable source, List <string> caption)
{
//创建Excel文件的对象
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);
for (int i = 0; i < caption.Count; i++)
{
var element = caption[i];
row1.CreateCell(i).SetCellValue(element);
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < source.Rows.Count; i++)
{
var dataRow = source.Rows[i];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < caption.Count; j++)
{
var keyValue = caption[j];
rowtemp.CreateCell(j).SetCellValue(dataRow[j].ToString());
}
}
// 写入到客户端
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 = "查询结果" + dateTime + ".xls";
return(ms);
//return File(ms, "application/vnd.ms-excel", fileName);
}
public void NpoiExcel(System.Data.DataTable dt, string file)
{
try
{
double sheetCountdbl = double.Parse(dt.Rows.Count.ToString()) / 60000;
int sheetCount = (int)(Math.Ceiling(sheetCountdbl));
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
for (int c = 0; c < sheetCount; c++)
{
string sheetname = "Sheet" + (c + 1).ToString();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
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);
}
int js = 0;//计数
//表内容
for (int i = c * 60000; i < dt.Rows.Count; i++)
{
if (js > 59999)
{
break;
}
IRow row = sheet.CreateRow(js + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
cell.CellStyle = style;
cell.SetCellValue(dt.Rows[i][j].ToString());
}
js++;
}
}
FileStream fs = File.Create(file);
book.Write(fs);
fs.Flush();
fs.Close();
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
}
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 ShunFengExcel()
{
try
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ExamUserInfoModel models = new ExamUserInfoModel();
models.GetUserInfo(null);
//获取list数据
var tlst = models.ListUserInfo;
//给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("最近一次绩效成绩要求");
//将数据逐步写入sheet1各个行
for (int i = 0; i < tlst.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(tlst[i].UserCode); //工号
rowtemp.CreateCell(1).SetCellValue(tlst[i].UserName); //姓名
rowtemp.CreateCell(2).SetCellValue(tlst[i].EntryDate.ToString()); //入职日期
rowtemp.CreateCell(3).SetCellValue(tlst[i].RankName); //职等
rowtemp.CreateCell(4).SetCellValue(tlst[i].PostName); //职位
rowtemp.CreateCell(5).SetCellValue(tlst[i].SkillLevel); //本职等对应技能等级
rowtemp.CreateCell(6).SetCellValue(tlst[i].CurrentSkillLevel); //已经考取技能等级
rowtemp.CreateCell(7).SetCellValue(tlst[i].ExamScore); //最近一次理论考试成绩
rowtemp.CreateCell(8).SetCellValue(tlst[i].LastExamTime.ToString()); //最近一次通过理论时间
rowtemp.CreateCell(9).SetCellValue(tlst[i].TheoreticalAchievement.ToString()); //最近一次实践成绩
rowtemp.CreateCell(10).SetCellValue(tlst[i].PracticeTime.ToString()); //最近一次实践考核通过时间
rowtemp.CreateCell(11).SetCellValue(tlst[i].HighestTestSkill); //最高可考技能等级
rowtemp.CreateCell(12).SetCellValue(tlst[i].ApplicationLevel); //可申请技能等级
rowtemp.CreateCell(13).SetCellValue(tlst[i].Achievement); //最近一次绩效成绩要求
}
// 写入到客户端
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"));
}
catch (Exception)
{
throw;
}
}
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 TestShiftRow0()
{
HSSFWorkbook b = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = b.CreateSheet();
s.CreateRow(0).CreateCell(0).SetCellValue("TEST1");
s.CreateRow(3).CreateCell(0).SetCellValue("TEST2");
s.ShiftRows(0, 4, 1);
}
public FileResult Export(Pagination rq, AdminCredential User)
{
#region 报表导出
var report = service.Get(rq.vid).Data;
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = (short)24;// NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
style.FillPattern = FillPattern.SolidForeground;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont(); //创建一个字体样式对象
font.FontHeightInPoints = 10; //字体大小
font.FontName = "宋体"; //和excel里面的字体对应
font.Boldweight = short.MaxValue; //字体加粗
font.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
style.SetFont(font); //将字体样式赋给样式对象
NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(report.Name);
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.Height = 430;
var FieldList = JsonConvert.DeserializeObject <List <ViewFieldModel> >(service.GetReportColumns(rq.vid.Value));
int i = 0;
foreach (var item in FieldList)
{
ICell cell = row1.CreateCell(i);
cell.SetCellValue(item.Title);
cell.CellStyle = style;
i++;
}
rq.Filter = Session[rq.vid.ToString() + "Filter"].ToString();
DataTable dt = service.GetExportReportData(rq, User);
//将数据逐步写入sheet1各个行
for (int z = 0; z < dt.Rows.Count; z++)
{
var row = dt.Rows[z];
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(z + 1);
int c = 0;
foreach (var item in FieldList)
{
rowtemp.CreateCell(c++).SetCellValue(row[item.Field].ToString());
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", report.Name + ".xls"));
#endregion
}
//导出 dt,和表头都传
public Stream ExportExcel(DataTable dt, List <EColName> 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);
//组建表头
int index = 0;
foreach (var i in list)
{
row1.CreateCell(index).SetCellValue(i.col);
i.sortseq = index;
index = index + 1;
}
//将数据逐步写入sheet1各个行
double d0 = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int jj = 0; jj < list.Count; jj++)
{
string colname = (from EColName a in list where a.sortseq == jj select a.colname).ToArray()[0];
string coldata = dt.Rows[i][colname].ToString();
string dbtype = dt.Columns[colname].DataType.ToString();
if (dbtype == "System.Int32" || dbtype == "System.Int16" || dbtype == "System.Int64" || dbtype == "System.Decimal" || dbtype == "Double")
{
if (double.TryParse(coldata, out d0))
{
d0 = Convert.ToDouble(coldata);
}
rowtemp.CreateCell(jj).SetCellValue(d0);
}
else
{
rowtemp.CreateCell(jj).SetCellValue(coldata);
}
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
return(ms);
}
public void TestMoveCell()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
IRow row = sheet.CreateRow(0);
IRow rowB = sheet.CreateRow(1);
ICell cellA2 = rowB.CreateCell(0);
Assert.AreEqual(0, rowB.FirstCellNum);
Assert.AreEqual(0, rowB.FirstCellNum);
Assert.AreEqual(-1, row.LastCellNum);
Assert.AreEqual(-1, row.FirstCellNum);
ICell cellB2 = row.CreateCell(1);
ICell cellB3 = row.CreateCell(2);
ICell cellB4 = row.CreateCell(3);
Assert.AreEqual(1, row.FirstCellNum);
Assert.AreEqual(4, row.LastCellNum);
// Try to move to somewhere else that's used
try
{
row.MoveCell(cellB2, (short)3);
Assert.Fail("ArgumentException should have been thrown");
}
catch (ArgumentException)
{
// expected during successful Test
}
// Try to move one off a different row
try
{
row.MoveCell(cellA2, (short)3);
Assert.Fail("ArgumentException should have been thrown");
}
catch (ArgumentException)
{
// expected during successful Test
}
// Move somewhere spare
Assert.IsNotNull(row.GetCell(1));
row.MoveCell(cellB2, (short)5);
Assert.IsNull(row.GetCell(1));
Assert.IsNotNull(row.GetCell(5));
Assert.AreEqual(5, cellB2.ColumnIndex);
Assert.AreEqual(2, row.FirstCellNum);
Assert.AreEqual(6, row.LastCellNum);
}
public void Test44636()
{
// Open the existing file, tweak one value and
// re-calculate
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44636.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
IRow row = sheet.GetRow(0);
row.GetCell(0).SetCellValue(4.2);
row.GetCell(2).SetCellValue(25);
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
Assert.AreEqual(4.2 * 25, row.GetCell(3).NumericCellValue, 0.0001);
if (OUTPUT_TEST_FILES)
{
// Save
FileStream existing = File.Open(tmpDirName + "44636-existing.xls", FileMode.Open);
existing.Seek(0, SeekOrigin.End);
wb.Write(existing);
existing.Close();
Console.Error.WriteLine("Existing file for bug #44636 written to " + existing.ToString());
}
wb.Close();
// Now, do a new file from scratch
wb = new HSSFWorkbook();
sheet = wb.CreateSheet();
row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(1.2);
row.CreateCell(1).SetCellValue(4.2);
row = sheet.CreateRow(1);
row.CreateCell(0).CellFormula = ("SUM(A1:B1)");
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
Assert.AreEqual(5.4, row.GetCell(0).NumericCellValue, 0.0001);
if (OUTPUT_TEST_FILES)
{
// Save
FileStream scratch = File.Open(tmpDirName + "44636-scratch.xls", FileMode.Open);
scratch.Seek(0, SeekOrigin.End);
wb.Write(scratch);
scratch.Close();
Console.Error.WriteLine("New file for bug #44636 written to " + scratch.ToString());
}
wb.Close();
}