private static HSSFWorkbook CreateWBA()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("Sheet1");
ISheet sheet2 = wb.CreateSheet("Sheet2");
ISheet sheet3 = wb.CreateSheet("John's sales");
CreateDataRow(sheet1, 0, 11, 12, 13, 14);
CreateDataRow(sheet1, 1, 21, 22, 23, 24);
CreateDataRow(sheet1, 2, 31, 32, 33, 34);
CreateDataRow(sheet2, 0, 50, 55, 60, 65);
CreateDataRow(sheet2, 1, 51, 56, 61, 66);
CreateDataRow(sheet2, 2, 52, 57, 62, 67);
CreateDataRow(sheet3, 0, 30, 31, 32);
CreateDataRow(sheet3, 1, 33, 34, 35);
IName name1 = wb.CreateName();
name1.NameName = ("sales1");
name1.RefersToFormula = ("Sheet1!A1:D1");
IName name2 = wb.CreateName();
name2.NameName = ("sales2");
name2.RefersToFormula = ("Sheet2!B1:C3");
IRow row = sheet1.CreateRow(3);
row.CreateCell(0).SetCellValue("sales1"); //A4
row.CreateCell(1).SetCellValue("sales2"); //B4
return wb;
}
static void Main(string[] args)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet s1=workbook.CreateSheet("Sheet1");
//set A2
s1.CreateRow(1).CreateCell(0).SetCellValue(-5);
//set B2
s1.GetRow(1).CreateCell(1).SetCellValue(1111);
//set C2
s1.GetRow(1).CreateCell(2).SetCellValue(7.623);
//set A3
s1.CreateRow(2).CreateCell(0).SetCellValue(2.2);
//set A4=A2+A3
s1.CreateRow(3).CreateCell(0).CellFormula = "A2+A3";
//set D2=SUM(A2:C2);
s1.GetRow(1).CreateCell(3).CellFormula = "SUM(A2:C2)";
//set A5=cos(5)+sin(10)
s1.CreateRow(4).CreateCell(0).CellFormula="cos(5)+sin(10)";
//create another sheet
ISheet s2 = workbook.CreateSheet("Sheet2");
//set cross-sheet reference
s2.CreateRow(0).CreateCell(0).CellFormula = "Sheet1!A2+Sheet1!A3";
IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(workbook);
var cell = e.Evaluate(cell);
WriteToFile();
}
static void Main(string[] args)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
//here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
//So we insert three sheet just like what Excel does
hssfworkbook.CreateSheet("Sheet1");
hssfworkbook.CreateSheet("Sheet2");
hssfworkbook.CreateSheet("Sheet3");
hssfworkbook.CreateSheet("Sheet4");
((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(@"test.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
public static MemoryStream CreateExcel2003StreamMulti(List <ExcelDataWapperEntity> dataentity, bool inOneSheet)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
int tick = 1;
if (inOneSheet)
{
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
for (int i = 0; i < dataentity.Count; i++)
{
int datastartrow = 0;
if (i > 0)
{
datastartrow = dataentity[i - 1].DataSource.Rows.Count + (string.IsNullOrEmpty(dataentity[i - 1].DataSourceDesc) ? 2 : 3);
}
WriteData(ref sheet1, dataentity[i].DataSource, dataentity[i].ColumnInfo, dataentity[i].DataSourceDesc, datastartrow);
}
}
else
{
foreach (ExcelDataWapperEntity w in dataentity)
{
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(w.DataSourceTitle ?? string.Format("Sheet{0}", tick));
WriteData(ref sheet1, w.DataSource, w.ColumnInfo, w.DataSourceDesc);
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
return(ms);
}
public void ProcessRequest(HttpContext context)
{
string projectName = context.Request["pname"].Trim();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet projectSheet = workbook.CreateSheet("项目各报销项汇总");
HSSFSheet projectReimSummarySheet = workbook.CreateSheet("项目报销项金额汇总");
HSSFSheet projectSummarySheet = workbook.CreateSheet("项目报销总金额汇总");
List<ProjectCategory> queryCategoryList = new List<ProjectCategory>();
DalOperationAboutReimItem dalReimItem = new DalOperationAboutReimItem();
List<ReimItem> reimItemList;
if (!string.IsNullOrWhiteSpace(projectName))
{
reimItemList = dalReimItem.GetReimItems(projectName.Trim(), 0, 0);
}
else
{
reimItemList = dalReimItem.GetAllDistinctReimItems();
}
List<ReimItem> reimItemSummaryList = dalReimItem.GetReimItemSummaryValues(projectName, 0, 0, null);
List<ReimItem> projectReimSummaryList = dalReimItem.GetProjectReimItems(projectName, 0);
SetProjectReimSheet(projectSheet, reimItemList);
SetProjectReimSummarySheet(projectReimSummarySheet, reimItemSummaryList);
SetProjectSummarySheet(projectSummarySheet, projectReimSummaryList);
string fileName = "项目报销项汇总";
System.IO.FileStream file = new System.IO.FileStream(HttpContext.Current.Server.MapPath(fileName + ".xls"), System.IO.FileMode.Create);
workbook.Write(file);
file.Dispose();
////插入值
FileInfo DownloadFile = new FileInfo(context.Server.MapPath(fileName + ".xls"));
context.Response.Clear();
context.Response.ClearHeaders();
context.Response.Buffer = false;
Encoding code = Encoding.GetEncoding("gb2312");
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.HeaderEncoding = code;//这句很重要
context.Response.ContentType = "application/octet-stream";
context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
context.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
context.Response.WriteFile(DownloadFile.FullName);
if (File.Exists(context.Server.MapPath(fileName + ".xls")))
{
File.Delete(context.Server.MapPath(fileName + ".xls"));
}
context.Response.Flush();
}
public void NpoiExcel(DataTable dt, DataTable dt2, string title)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("物料汇总");
NPOI.SS.UserModel.ISheet sheet2 = book.CreateSheet("类别汇总");
NPOI.SS.UserModel.IRow headerrow1 = sheet1.CreateRow(0);
NPOI.SS.UserModel.IRow headerrow2 = sheet2.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 = headerrow1.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int I = 0; I <= dt.Rows.Count - 1; I++)
{
HSSFRow row2 = (HSSFRow)sheet1.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);
sheet1.SetColumnWidth(j, 20 * 150);
}
}
for (int i = 0; i < dt2.Columns.Count; i++)
{
ICell cell = headerrow2.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt2.Columns[i].ColumnName);
}
for (int I = 0; I <= dt2.Rows.Count - 1; I++)
{
HSSFRow row2 = (HSSFRow)sheet2.CreateRow(I + 1);
for (int j = 0; j <= dt2.Columns.Count - 1; j++)
{
string DgvValue = dt2.Rows[I][j].ToString();
row2.CreateCell(j).SetCellValue(DgvValue);
sheet2.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 TestSetRepeatingRowsAndColumnsBug29747()
{
HSSFWorkbook wb = new HSSFWorkbook();
wb.CreateSheet();
wb.CreateSheet();
HSSFSheet sheet2 = (HSSFSheet)wb.CreateSheet();
sheet2.RepeatingRows = (CellRangeAddress.ValueOf("1:2"));
NameRecord nameRecord = wb.Workbook.GetNameRecord(0);
Assert.AreEqual(3, nameRecord.SheetNumber);
}
public void TestSetRepeatingRowsAndColumns()
{
// Test bug 29747
HSSFWorkbook b = new HSSFWorkbook();
b.CreateSheet();
b.CreateSheet();
b.CreateSheet();
b.SetRepeatingRowsAndColumns(2, 0, 1, -1, -1);
NameRecord nameRecord = b.Workbook.GetNameRecord(0);
Assert.AreEqual(3, nameRecord.SheetNumber);
}
static void Main(string[] args)
{
hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("new sheet");
ISheet sheet2 = hssfworkbook.CreateSheet("second sheet");
//Create a split with the lower left side being the active quadrant
sheet2.CreateSplitPane(2000, 2000, 0, 0, PanePosition.LowerLeft);
//Write the stream data of workbook to the root directory
FileStream file = new FileStream(@"output-Split.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
/// <summary>
/// 导出数据
/// </summary>
/// <returns></returns>
public void ExportData()
{
string ProductBZ = System.Web.HttpContext.Current.Request.Params["ProductBZ"];
HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet("sheet1");
IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("账号");
row1.CreateCell(1).SetCellValue("密码");
row1.CreateCell(2).SetCellValue("姓名");
Dictionary <string, object> dicWhere = new Dictionary <string, object>();
new CreateCriteria(dicWhere).Add(ORMRestrictions <string> .Eq("ProductBZ", ProductBZ));
IList <QTProductUserModel> qTProductUsers = QTProductUserService.ServiceHelper.LoadWithPageInfinity("GQT.QT.ALLProductUsers", dicWhere).Results;
//List<BudgetAccountsModel> models = budgetAccounts.ToList();
for (int i = 0; i < qTProductUsers.Count; i++)
{
QTProductUserModel model = qTProductUsers[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(model.ProductUserCode);
row.CreateCell(1).SetCellValue(model.ProductUserPwd);
row.CreateCell(2).SetCellValue(model.ProductUserName);
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
//excel导出，最多只能导出65535条记录，没有考虑分sheet页，后续如果数据量较多再扩充
public static void writeExcel(DataTable dt, ExcelInfo ei, String[] colTitle, int[] colWidth)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(ei.sheetName);
HSSFRow rowtitle = (HSSFRow)sheet.CreateRow(0);
for (int j = 0; j < colTitle.Length;j++)
{
//设置列宽
sheet.SetColumnWidth(j, colWidth[j] * 255);
rowtitle.CreateCell(j).SetCellValue(colTitle[j]);
}
int i = 1;
foreach (DataRow row in dt.Rows)
{
HSSFRow newrow = (HSSFRow)sheet.CreateRow(i);
int col = 0;
for (; col < dt.Columns.Count; col++)
newrow.CreateCell(col).SetCellValue(Convert.ToString(row[col]));
i++;
}
try
{
using (Stream stream = File.Open(ei.filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(stream);
}
}
catch (Exception e)
{
throw e;
}
}
public void TestPolygonPoints()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());
polygon.SetPolygonDrawArea(100, 100);
polygon.SetPoints(new int[] { 0, 90, 50, 90 }, new int[] { 5, 5, 44, 88 });
PolygonShape polygonShape = HSSFTestModelHelper.CreatePolygonShape(0, polygon);
EscherArrayProperty verticesProp1 = polygon.GetOptRecord().Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;
EscherArrayProperty verticesProp2 = ((EscherOptRecord)polygonShape.SpContainer.GetChildById(EscherOptRecord.RECORD_ID))
.Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;
Assert.AreEqual(verticesProp1.NumberOfElementsInArray, verticesProp2.NumberOfElementsInArray);
Assert.AreEqual(verticesProp1.ToXml(""), verticesProp2.ToXml(""));
polygon.SetPoints(new int[] { 1, 2, 3 }, new int[] { 4, 5, 6 });
Assert.IsTrue(Arrays.Equals(polygon.XPoints, new int[] { 1, 2, 3 }));
Assert.IsTrue(Arrays.Equals(polygon.YPoints, new int[] { 4, 5, 6 }));
polygonShape = HSSFTestModelHelper.CreatePolygonShape(0, polygon);
verticesProp1 = polygon.GetOptRecord().Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;
verticesProp2 = ((EscherOptRecord)polygonShape.SpContainer.GetChildById(EscherOptRecord.RECORD_ID))
.Lookup(EscherProperties.GEOMETRY__VERTICES) as EscherArrayProperty;
Assert.AreEqual(verticesProp1.NumberOfElementsInArray, verticesProp2.NumberOfElementsInArray);
Assert.AreEqual(verticesProp1.ToXml(""), verticesProp2.ToXml(""));
}
public override void Write(Stream outStream)
{
Workbook workbook = new HSSFWorkbook();
IEnumerable<User> users = User.SelectAll();
foreach (User user in users)
{
IEnumerable<TimeBlock> timeBlocks = TimeBlock.SelectByUserAndDateRange(user.UserId, FromDate, ToDate);
if (timeBlocks.Count() > 0)
{
Sheet sheet = workbook.CreateSheet(user.Name);
Row header = sheet.CreateRow(0);
header.CreateCell(0).SetCellValue("Resource");
header.CreateCell(1).SetCellValue("Date");
header.CreateCell(2).SetCellValue("Client");
header.CreateCell(3).SetCellValue("Work Package");
header.CreateCell(4).SetCellValue("Hours");
int i = 1;
foreach (TimeBlock timeBlock in timeBlocks)
{
Row row = sheet.CreateRow(i ++);
row.CreateCell(0).SetCellValue(timeBlock.User.Name);
row.CreateCell(1).SetCellValue(timeBlock.Date.ToString("dd/MM/yy"));
row.CreateCell(2).SetCellValue(timeBlock.Job.Client.Name);
row.CreateCell(3).SetCellValue(timeBlock.JobId);
row.CreateCell(4).SetCellValue(Convert.ToDouble(timeBlock.Time));
}
}
}
workbook.Write(outStream);
}
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[] GetExcelBufferFromDataTable(DataTable dt)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
Sheet sheet = hssfworkbook.CreateSheet("Sheet1");
sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.GetRow(0).CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 1; i <= dt.Rows.Count; i++)
{
sheet.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
sheet.GetRow(i).CreateCell(j).SetCellValue(dt.Rows[i - 1][dt.Columns[j].ColumnName].ToString());
}
}
MemoryStream memstream = new MemoryStream();
try
{
hssfworkbook.Write(memstream);
byte[] buffer = memstream.GetBuffer();
return buffer;
}
finally
{
memstream.Close();
}
}
public void Test44916()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
// 1. Create drawing patriarch
IDrawing patr = sheet.CreateDrawingPatriarch();
// 2. Try to re-get the patriarch
IDrawing existingPatr;
try
{
existingPatr = sheet.DrawingPatriarch;
}
catch (NullReferenceException)
{
throw new AssertionException("Identified bug 44916");
}
// 3. Use patriarch
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 600, 245, (short)1, 1, (short)1, 2);
anchor.AnchorType = (AnchorType)(3);
byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png");
int idx1 = wb.AddPicture(pictureData, PictureType.PNG);
patr.CreatePicture(anchor, idx1);
// 4. Try to re-use patriarch later
existingPatr = sheet.DrawingPatriarch;
Assert.IsNotNull(existingPatr);
}
/// <summary>
/// 导出数据
/// </summary>
/// <returns></returns>
public void ExportData()
{
HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet("sheet1");
IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("科目代码");
row1.CreateCell(1).SetCellValue("科目名称");
row1.CreateCell(2).SetCellValue("科目类别");
IList <BudgetAccountsModel> budgetAccounts = BudgetAccountsService.ExportData();
//List<BudgetAccountsModel> models = budgetAccounts.ToList();
for (int i = 0; i < budgetAccounts.Count; i++)
{
BudgetAccountsModel model = budgetAccounts[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(model.KMDM);
row.CreateCell(1).SetCellValue(model.KMMC);
row.CreateCell(2).SetCellValue(model.KMLB == "0" ? "收入" : "支出");
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
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);
}
/// <summary>
/// 导出数据
/// </summary>
/// <returns></returns>
public void ExportData()
{
//string ProductBZ = System.Web.HttpContext.Current.Request.Params["ProductBZ"];
HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet("sheet1");
IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("代码");
row1.CreateCell(1).SetCellValue("名称");
//row1.CreateCell(2).SetCellValue("备注");
Dictionary <string, object> dicWhere = new Dictionary <string, object>();
new CreateCriteria(dicWhere)
.Add(ORMRestrictions <System.Int64> .NotEq("PhId", 0));
IList <ProcurementCatalogModel> procurementCatalogs = ProcurementCatalogService.Find(dicWhere).Data;
for (int i = 0; i < procurementCatalogs.Count; i++)
{
ProcurementCatalogModel model = procurementCatalogs[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(model.FCode);
row.CreateCell(1).SetCellValue(model.FName);
//row.CreateCell(2).SetCellValue(model.FRemark);
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
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();
}
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 void TestEvaluateInSheetExample2()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
sheet.CreateRow(1).CreateCell(0).SetCellValue(0.08d);
sheet.CreateRow(2).CreateCell(0).SetCellValue(-40000d);
sheet.CreateRow(3).CreateCell(0).SetCellValue(8000d);
sheet.CreateRow(4).CreateCell(0).SetCellValue(9200d);
sheet.CreateRow(5).CreateCell(0).SetCellValue(10000d);
sheet.CreateRow(6).CreateCell(0).SetCellValue(12000d);
sheet.CreateRow(7).CreateCell(0).SetCellValue(14500d);
ICell cell = row.CreateCell(8);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
// Enumeration
cell.CellFormula = ("NPV(A2, A4,A5,A6,A7,A8)+A3");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cell);
double res = cell.NumericCellValue;
Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);
// Range
cell.CellFormula = ("NPV(A2, A4:A8)+A3");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cell);
res = cell.NumericCellValue;
Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);
}
public static void WriteExcel(List <DataTable> dts, string filePath)
{
try
{
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("sheet1");
createColumnName(book, sheet);
createCells(book, sheet, dts[0], 0, 1);
createCells(book, sheet, dts[1], 3, 4);
createCells(book, sheet, dts[2], 6, 7);
createCells(book, sheet, dts[3], 9, 10);
createCells(book, sheet, dts[4], 12, 14);
// 写入到客户端
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;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public void TestCellWithMerges()
{
IWorkbook wb = new HSSFWorkbook();
ISheet s = wb.CreateSheet();
// Create some test data
IRow r2 = s.CreateRow(1);
r2.CreateCell(0).SetCellValue(10);
r2.CreateCell(1).SetCellValue(11);
IRow r3 = s.CreateRow(2);
r3.CreateCell(0).SetCellValue(20);
r3.CreateCell(1).SetCellValue(21);
s.AddMergedRegion(new CellRangeAddress(2, 3, 0, 0));
s.AddMergedRegion(new CellRangeAddress(2, 2, 1, 4));
// With a cell that isn't defined, we'll Get null
Assert.AreEqual(null, SheetUtil.GetCellWithMerges(s, 0, 0));
// With a cell that's not in a merged region, we'll Get that
Assert.AreEqual(10.0, SheetUtil.GetCellWithMerges(s, 1, 0).NumericCellValue);
Assert.AreEqual(11.0, SheetUtil.GetCellWithMerges(s, 1, 1).NumericCellValue);
// With a cell that's the primary one of a merged region, we Get that cell
Assert.AreEqual(20.0, SheetUtil.GetCellWithMerges(s, 2, 0).NumericCellValue);
Assert.AreEqual(21, SheetUtil.GetCellWithMerges(s, 2, 1).NumericCellValue);
// With a cell elsewhere in the merged region, Get top-left
Assert.AreEqual(20.0, SheetUtil.GetCellWithMerges(s, 3, 0).NumericCellValue);
Assert.AreEqual(21.0, SheetUtil.GetCellWithMerges(s, 2, 2).NumericCellValue);
Assert.AreEqual(21.0, SheetUtil.GetCellWithMerges(s, 2, 3).NumericCellValue);
Assert.AreEqual(21.0, SheetUtil.GetCellWithMerges(s, 2, 4).NumericCellValue);
}
/// <summary>
/// 导出数据
/// </summary>
/// <returns></returns>
public void ExportData()
{
HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = book.CreateSheet("sheet1");
IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("项目库组织代码");
row1.CreateCell(1).SetCellValue("老G6H组织代码");
Dictionary <string, object> dicwhere = new Dictionary <string, object>();
new CreateCriteria(dicwhere)
.Add(ORMRestrictions <System.Int64> .NotEq("PhId", 0));
IList <QtOrgDygxModel> qtOrgDygxes = QtOrgDygxService.Find(dicwhere).Data;//数据库的所有数据
for (int i = 0; i < qtOrgDygxes.Count; i++)
{
QtOrgDygxModel model = qtOrgDygxes[i];
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(model.Xmorg);
row.CreateCell(1).SetCellValue(model.Oldorg);
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
public IWorkbook GenerateSheet(DataTable dt, string sheetname)
{
IWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet(sheetname);
IRow row = sheet1.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName == "ID")
{
continue;
}
row.CreateCell(i, CellType.STRING).SetCellValue(dt.Columns[i].ColumnName);
}
for (int j = 0; j < dt.Rows.Count; j++)
{
IRow row1 = sheet1.CreateRow(j+1);
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName=="ID")
{
continue;
}
row1.CreateCell(i, CellType.STRING).SetCellValue(dt.Rows[j ][i].ToString());
}
}
return hssfworkbook;
}
public void TestCreateCFRuleRecord()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
CFRuleRecord record = CFRuleRecord.Create(sheet, "7");
TestCFRuleRecord1(record);
// Serialize
byte[] SerializedRecord = record.Serialize();
// Strip header
byte[] recordData = new byte[SerializedRecord.Length - 4];
Array.Copy(SerializedRecord, 4, recordData, 0, recordData.Length);
// DeSerialize
record = new CFRuleRecord(TestcaseRecordInputStream.Create(CFRuleRecord.sid, recordData));
// Serialize again
byte[] output = record.Serialize();
// Compare
Assert.AreEqual(recordData.Length + 4, output.Length, "Output size"); //includes sid+recordlength
for (int i = 0; i < recordData.Length; i++)
{
Assert.AreEqual(recordData[i], output[i + 4], "CFRuleRecord doesn't match");
}
}
/// <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);
}
//数据导出为Excel
private void btnExport_Click(object sender, EventArgs e)
{
string sql = "select CC_AutoId,CC_CustomerName,CC_CellPhone,CC_Landline,CC_BuyDate,CC_CarNum,CC_BracketNum from T_Customers";
using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text))
{
if (reader.HasRows)
{
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("customers");
int rowIndex = 0;//手动处理行索引
while (reader.Read())
{
int autoId = reader.GetInt32(0);
string name = reader.GetString(1);
string cellPhone = reader.GetString(2);
//数据为空,那么就不能Get..,所以要在这里判断
string landLine = reader.IsDBNull(3) ? null : reader.GetString(3);//可为空
DateTime BuyDate = reader.GetDateTime(4);
string carNum = reader.GetString(5);
string bracketNum = reader.GetString(6);
IRow row = sheet.CreateRow(rowIndex);
rowIndex++;
row.CreateCell(0).SetCellValue(autoId);
row.CreateCell(1).SetCellValue(name);
row.CreateCell(2).SetCellValue(cellPhone);
//如果为空或Null,把Excel这个单元格设置为值为空
if (string.IsNullOrEmpty(landLine))
{
row.CreateCell(3).SetCellType(CellType.BLANK);
}
else
{
row.CreateCell(3).SetCellValue(landLine);
}
//============设置日期格式=======
//创建单元格样式对象
ICellStyle cellStyle = wb.CreateCellStyle();
//设置单元格样式对象属性
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
ICell BuyDateRow = row.CreateCell(4);
BuyDateRow.CellStyle = cellStyle;
BuyDateRow.SetCellValue(BuyDate);
//==============设置完成========
row.CreateCell(5).SetCellValue(carNum);
row.CreateCell(6).SetCellValue(bracketNum);
}
using (FileStream fs = File.OpenWrite("Customers.xls"))
{
wb.Write(fs);
MessageBox.Show("ok");
}
}
}
}
//将一个集合中的数据写入到Excel
private void btnInclude_Click(object sender, EventArgs e)
{
//对象初始化器
List<Person> list = new List<Person>(){
new Person(){Name="路人甲",Age=18,Mail="*****@*****.**"},
new Person(){Name="路人乙",Age=28,Mail="*****@*****.**"},
new Person(){Name="路人丙",Age=38,Mail="*****@*****.**"},
};
//创建工作簿
IWorkbook wb = new HSSFWorkbook();
//创建工作表
ISheet sheet = wb.CreateSheet("测试");
//创建行
for (int i = 0; i < list.Count();i++ )
{
IRow row=sheet.CreateRow(i);
//创建单元格
row.CreateCell(0).SetCellValue(list[i].Name);
row.CreateCell(1).SetCellValue(list[i].Age);
row.CreateCell(2).SetCellValue(list[i].Mail);
}
using (FileStream fs=File.OpenWrite("NewTestExcel.xls"))
{
wb.Write(fs);
}
MessageBox.Show("Ok");
}
public static Workbook CreateWorkBookWithSheet()
{
Workbook workbook = new HSSFWorkbook();
workbook.CreateSheet();
return workbook;
}
public void TestEvaluateMissingArgs()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
ISheet sheet = wb.CreateSheet("Sheet1");
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellFormula=("if(true,)");
fe.ClearAllCachedResultValues();
CellValue cv;
try
{
cv = fe.Evaluate(cell);
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
throw new AssertionException("Missing args Evaluation not implemented (bug 43354");
}
// MissingArg -> BlankEval -> zero (as formula result)
Assert.AreEqual(0.0, cv.NumberValue, 0.0);
// MissingArg -> BlankEval -> empty string (in concatenation)
cell.CellFormula=("\"abc\"&if(true,)");
fe.ClearAllCachedResultValues();
Assert.AreEqual("abc", fe.Evaluate(cell).StringValue);
}
public void TestWriteSheetFont()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
//ICell c = null;
IFont fnt = wb.CreateFont();
NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();
fnt.Color=(NPOI.HSSF.Util.HSSFColor.RED.index);
fnt.Boldweight=(short)FontBoldWeight.BOLD;
cs.SetFont(fnt);
for (short rownum = (short)0; rownum < 100; rownum++)
{
r = s.CreateRow(rownum);
r.RowStyle=(cs);
r.CreateCell(0);
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
SanityChecker sanityChecker = new SanityChecker();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
public void TestSetGetCoordinates()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
Assert.AreEqual(group.X1, 0);
Assert.AreEqual(group.Y1, 0);
Assert.AreEqual(group.X2, 1023);
Assert.AreEqual(group.Y2, 255);
group.SetCoordinates(1, 2, 3, 4);
Assert.AreEqual(group.X1, 1);
Assert.AreEqual(group.Y1, 2);
Assert.AreEqual(group.X2, 3);
Assert.AreEqual(group.Y2, 4);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
group = (HSSFShapeGroup)patriarch.Children[(0)];
Assert.AreEqual(group.X1, 1);
Assert.AreEqual(group.Y1, 2);
Assert.AreEqual(group.X2, 3);
Assert.AreEqual(group.Y2, 4);
}
public void TestAddToExistingFile()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFShapeGroup group1 = patriarch.CreateGroup(new HSSFClientAnchor());
HSSFShapeGroup group2 = patriarch.CreateGroup(new HSSFClientAnchor());
group1.SetCoordinates(1, 2, 3, 4);
group2.SetCoordinates(5, 6, 7, 8);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 2);
HSSFShapeGroup group3 = patriarch.CreateGroup(new HSSFClientAnchor());
group3.SetCoordinates(9, 10, 11, 12);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
Assert.AreEqual(patriarch.Children.Count, 3);
}
/// <summary>
/// 生成导会员卡信息的excel文件，并返回导出的url地址
/// </summary>
/// <param name="entities">会员卡信息集合</param>
/// <returns>导出excel的url</returns>
public string Export <T>(IList <T> entities, string sheetName = "导出数据")
{
SetPropertyInfo(typeof(T));
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
_book = book;
ISheet sheet = book.CreateSheet(sheetName);
if (OnSheetCreated != null)
{
OnSheetCreated(sheet);
}
FillHead(sheet);
for (var i = 0; i < entities.Count(); i++)
{
FillRow(entities[i], sheet, i + 1);
}
using (var ms = new MemoryStream())
{
book.Write(ms);
var fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
using (var fs = new FileStream(ExportTempPath(fileName), FileMode.Create))
{
book.Write(fs);
}
return(ExportTempUrl(fileName));
}
}
/// <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);
//写入到客户端
}
public void TestGetAnchorHeightInPoints()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Test");
HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
float p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(12.7, p, 0.001);
sheet.CreateRow(0).HeightInPoints = (14);
a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(13.945, p, 0.001);
a = new HSSFClientAnchor(0, 0, 1023, 127, (short)0, 0, (short)0, 0);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(6.945, p, 0.001);
a = new HSSFClientAnchor(0, 126, 1023, 127, (short)0, 0, (short)0, 0);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(0.054, p, 0.001);
a = new HSSFClientAnchor(0, 0, 1023, 0, (short)0, 0, (short)0, 1);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(14.0, p, 0.001);
sheet.CreateRow(0).HeightInPoints = (12);
a = new HSSFClientAnchor(0, 127, 1023, 127, (short)0, 0, (short)0, 1);
p = a.GetAnchorHeightInPoints(sheet);
Assert.AreEqual(12.372, p, 0.001);
}
public void TestClean1()
{
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell = wb.CreateSheet().CreateRow(0).CreateCell(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
String[] asserts = {
"aniket\u0007\u0017\u0019", "aniket",
"\u0011aniket\u0007\u0017\u0010", "aniket",
"\u0011aniket\u0007\u0017\u007F", "aniket\u007F",
"\u2116aniket\u2211\uFB5E\u2039", "\u2116aniket\u2211\uFB5E\u2039",
};
for (int i = 0; i < asserts.Length; i += 2)
{
String formulaText = "CLEAN(\"" + asserts[i] + "\")";
ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
}
asserts = new String[] {
"CHAR(7)&\"text\"&CHAR(7)", "text",
"CHAR(7)&\"text\"&CHAR(17)", "text",
"CHAR(181)&\"text\"&CHAR(190)", "\u00B5text\u00BE",
"\"text\"&CHAR(160)&\"'\"", "text\u00A0'",
};
for (int i = 0; i < asserts.Length; i += 2)
{
String formulaText = "CLEAN(" + asserts[i] + ")";
ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
}
}
public void TestInSpreadSheet()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellFormula=("B1%");
row.CreateCell(1).SetCellValue(50.0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv;
try
{
cv = fe.Evaluate(cell);
}
catch (SystemException e)
{
if (e.InnerException is NullReferenceException)
{
throw new AssertionException("Identified bug 44608");
}
// else some other unexpected error
throw e;
}
Assert.AreEqual(CellType.NUMERIC, cv.CellType);
Assert.AreEqual(0.5, cv.NumberValue, 0.0);
}
/**
* Set up a calculation workbook with input cells nicely segregated on a
* sheet called "Inputs"
*/
private static HSSFWorkbook CreateWorkbook()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("Inputs");
ISheet sheet2 = wb.CreateSheet("Calculations");
IRow row;
row = sheet2.CreateRow(0);
row.CreateCell(0).CellFormula = ("B1*Inputs!A1-Inputs!B1");
row.CreateCell(1).SetCellValue(5.0); // Calculations!B1
// some default input values
row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue(2.0); // Inputs!A1
row.CreateCell(1).SetCellValue(3.0); // Inputs!B1
return wb;
}
public void TestEvaluate()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sh = wb.CreateSheet();
ICell cell1 = sh.CreateRow(0).CreateCell(0);
cell1.CellFormula = ("MROUND(10, 3)");
ICell cell2 = sh.CreateRow(0).CreateCell(0);
cell2.CellFormula = ("MROUND(-10, -3)");
ICell cell3 = sh.CreateRow(0).CreateCell(0);
cell3.CellFormula = ("MROUND(1.3, 0.2)");
ICell cell4 = sh.CreateRow(0).CreateCell(0);
cell4.CellFormula = ("MROUND(5, -2)");
ICell cell5 = sh.CreateRow(0).CreateCell(0);
cell5.CellFormula = ("MROUND(5, 0)");
double accuracy = 1E-9;
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(9.0, Evaluator.Evaluate(cell1).NumberValue, accuracy, "Rounds 10 to a nearest multiple of 3 (9)");
Assert.AreEqual(-9.0, Evaluator.Evaluate(cell2).NumberValue, accuracy, "Rounds -10 to a nearest multiple of -3 (-9)");
Assert.AreEqual(1.4, Evaluator.Evaluate(cell3).NumberValue, accuracy, "Rounds 1.3 to a nearest multiple of 0.2 (1.4)");
Assert.AreEqual(ErrorEval.NUM_ERROR.ErrorCode, Evaluator.Evaluate(cell4).ErrorValue, "Returns an error, because -2 and 5 have different signs (#NUM!)");
Assert.AreEqual(0.0, Evaluator.Evaluate(cell5).NumberValue, "Returns 0 because the multiple is 0");
}
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 TestWriteSheetSimple() {
string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple",
".xls");
FileStream out1 = new FileStream(filepath,FileMode.OpenOrCreate);
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
for (int rownum = 0; rownum < 100; rownum++) {
r = s.CreateRow(rownum);
for (int cellnum = 0; cellnum < 50; cellnum += 2) {
c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ ((( double ) rownum / 1000)
+ (( double ) cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
wb.Write(out1);
out1.Close();
sanityChecker.CheckHSSFWorkbook(wb);
Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
}
/// <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);
}
/// <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);
//写入到客户端
}
/// <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"));
}
/// <summary>
/// 下载当前最新配置
/// </summary>
/// <returns></returns>
public ActionResult DownLoadFile()
{
var sourceList = _iswcmgr.GetAllSearchWord();
//创建Excel文件的对象
var book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
var sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
var row1 = sheet1.CreateRow(0);
var fileName = "最新配置-" + DateTime.Now.ToString("yyyyMMdd") + "-" + new Random().Next(999) + ".xls";
row1.CreateCell(0).SetCellValue("关键词");
row1.CreateCell(1).SetCellValue("同义词");
row1.CreateCell(2).SetCellValue("是否删除");
for (var i = 0; i < sourceList.Count; i++)
{
var rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(sourceList[i].TargetWord);
rowtemp.CreateCell(1).SetCellValue(sourceList[i].SourceWord);
rowtemp.CreateCell(2).SetCellValue("1");
}
// 写入到客户端
byte[] file;
using (var ms = new System.IO.MemoryStream())
{
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
file = ms.ToArray();
}
return(File(file, "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);
}
}
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;
}
}
private void btnOutput_Click(object sender, EventArgs e)
{
List <MODEL.Classes> lists = cm.GetAllClass(false);
/*
* 1.创建出工作薄
* 2.为这个工作薄创建出工作表
* 3.为这个表创建出行
* 4.为这个行创建出每一列(单元格cell)--添加数据
* 5.文件的写入
*/
//创建一个工作薄
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
//创建一张工作表
ISheet sheet1 = workbook.CreateSheet("sh1");
// workbook.CreateSheet("Sh1");
///NPOI.HSSF. .SS .UserModel.HSSFSheet sheet1 = workbook.CreateSheet(); //.CreateSheet("sh1");
////workbook.CreateSheet("sh1"); // workbook.CreateSheet("sh1");
//需要导出的数据在集合中，每一个对象对应着后期表的一行数据
for (int i = 0; i < lists.Count; i++)
{
//创建行
IRow row = sheet1.CreateRow(i);
//创建第一个单元格
ICell cell1 = row.CreateCell(0);
cell1.SetCellValue(lists[i].CID);
//创建第2个单元格
ICell cell2 = row.CreateCell(1);
cell2.SetCellValue(lists[i].CName);
//创建第3个单元格
ICell cell3 = row.CreateCell(2);
cell3.SetCellValue(lists[i].CCount);
//创建第4个单元格
ICell cell4 = row.CreateCell(3);
cell4.SetCellValue(lists[i].CImg);
//创建第5个单元格
ICell cell5 = row.CreateCell(4);
cell5.SetCellValue(lists[i].CIsDel);
//创建第6个单元格
ICell cell6 = row.CreateCell(5);
//cell6.SetCellValue(lists[i].CAddTime.ToString("yyyy年MM月dd日")); //日期值会被当成double
cell6.SetCellValue(lists[i].CAddTime);
//如何修改日期类型的格式
ICellStyle cs = workbook.CreateCellStyle();
IDataFormat df = workbook.CreateDataFormat();
cs.DataFormat = df.GetFormat("yyyy年MM月dd日");
cell6.CellStyle = cs;
}
using (FileStream fs = new FileStream("aa.xls", FileMode.Create))
{
workbook.Write(fs);
MessageBox.Show("ok");
}
}
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));
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="response"></param>
/// <param name="dt"></param>
/// <param name="fileName"></param>
/// <param name="sheetname"></param>
public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.IFont font = book.CreateFont();
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "微软雅黑";
style.SetFont(font);
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
}
}
//写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls"));
response.ContentType = "application/vnd.ms-excel";
//response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
//导出 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 FileResult Excel2()
{
MyElectrCheck_DBDataContext mdc = new MyElectrCheck_DBDataContext();
List <DataEliminate> list = new List <DataEliminate>();
if (Session["solvelist"] != null)
{
//将session转为集合
list = (List <DataEliminate>)Session["solvelist"];
}
NPOI.HSSF.UserModel.HSSFWorkbook Book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = Book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("任务编号");
row1.CreateCell(1).SetCellValue("任务名称");
row1.CreateCell(2).SetCellValue("线路编号");
row1.CreateCell(3).SetCellValue("线路编号");
row1.CreateCell(4).SetCellValue("有无故障");
row1.CreateCell(5).SetCellValue("缺陷级别");
row1.CreateCell(6).SetCellValue("缺陷类型");
row1.CreateCell(7).SetCellValue("消缺时间");
row1.CreateCell(8).SetCellValue("发现时间");
row1.CreateCell(9).SetCellValue("缺陷描述");
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(list[i].solveTaskCode);
rowtemp.CreateCell(1).SetCellValue(list[i].solveTaskName);
rowtemp.CreateCell(2).SetCellValue(list[i].lineCode);
if (list[i].isBug == 1)
{
rowtemp.CreateCell(3).SetCellValue("有");
}
else
{
rowtemp.CreateCell(3).SetCellValue("无");
}
rowtemp.CreateCell(4).SetCellValue(list[i].bugLevelName);
rowtemp.CreateCell(5).SetCellValue(list[i].bugLevelName);
rowtemp.CreateCell(6).SetCellValue(list[i].bugTypeName);
rowtemp.CreateCell(7).SetCellValue(list[i].finishTime.ToString());
rowtemp.CreateCell(8).SetCellValue(list[i].discoverTime.ToString());
rowtemp.CreateCell(9).SetCellValue(list[i].bugDesc);
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
Book.Write(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
DateTime dt = DateTime.Now;
string dateTime = dt.ToString("yyMMddHHmmssff");
string fileName = "查询结果" + dateTime + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public static MemoryStream CreateExcel2003Stream(DataTable dt, Dictionary <string, string> columnInfo)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
WriteData(ref sheet1, dt, columnInfo, string.Empty);
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
return(ms);
}
//导出 只传dt
public Stream ExportExcel(DataTable dt)
{
//创建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);
//组建表头
DataColumnCollection dcc = dt.Columns;
int index = 0;
foreach (DataColumn i in dcc)
{
row1.CreateCell(index).SetCellValue(i.ColumnName);
index = index + 1;
}
double d0 = 0;
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int jj = 0; jj < dcc.Count; jj++)
{
string dbtype = dcc[jj].DataType.ToString();
string coldata = dt.Rows[i][dcc[jj].ColumnName].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);
}
/// <summary>
/// Creates the excel worksheet.
/// </summary>
/// <param name="name">The sheet name.</param>
public void CreateWorksheet(string name)
{
if (_workbook == null)
{
CreateWorkbook("");
}
//Creating the Sheet which will show us all data
NPOI.SS.UserModel.Sheet sheet = _workbook.CreateSheet(name);
_sheet = sheet;
}
/// <summary>
/// 设置样式
/// </summary>
private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style)
{
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
NPOI.SS.UserModel.ICell cell = null;
NPOI.SS.UserModel.ICell newCell = null;
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.CellStyle = style;
cell.SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
string strColDataType = dt.Columns[j].DataType.ToString();
if (strColDataType.Equals("System.Int32"))
{
int intValue = 0;
int.TryParse(dt.Rows[i][j].ToString(), out intValue);
row2.CreateCell(j).SetCellValue(intValue);
}
else if (strColDataType.Equals("System.String"))
{
row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
else if (strColDataType.Equals("System.Double"))
{
double dblValue = 0;
double.TryParse(dt.Rows[i][j].ToString(), out dblValue);
row2.CreateCell(j).SetCellValue(dblValue);
}
else if (strColDataType.Equals("System.DateTime"))
{
DateTime dateV;
DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV);
newCell = row2.CreateCell(j);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-m-d");
newCell.CellStyle = cellStyle;
}
}
}
}
