public override void FillNoTemplateForm(HSSFWorkbook workBook, IEnumerable <T> data, string sheetName)
{
base.FillNoTemplateForm(workBook, data, sheetName);
HSSFSheet sheet = (HSSFSheet)workBook.GetSheet(sheetName);
int rowCount = HeadCount;
IRow row;
foreach (var item in data)
{
row = sheet.CreateRow(rowCount);
SetNoTemplateDataRowValue(workBook, row, item);
rowCount++;
}
}
public void TestRepeatingBug()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Design Variants");
IRow row = sheet.CreateRow(2);
ICell cell = row.CreateCell(1);
cell.SetCellValue(new HSSFRichTextString("Class"));
cell = row.CreateCell(2);
RecordCounter rc = new RecordCounter();
sheet.Sheet.VisitContainedRecords(rc, 0);
Assert.AreEqual(1, rc.GetCount());
}
public static void test1()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet2 = hssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA");
sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
HSSFName range = hssfworkbook.CreateName() as HSSFName;
range.RefersToFormula = "ShtDictionary!$A1:$A3";
range.NameName = "dicRange";
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
string workbookFile = @"D:\\wulei1.xls";
hssfworkbook = null;
FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
}
private static void CreateHeadRow(Dictionary <string, string> titleDic, HSSFSheet sheet, int[] arrColWidth, HSSFCellStyle headStyle)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
int colIndex = 0;
foreach (var dicItem in titleDic)
{
string columnName = dicItem.Value;
headerRow.CreateCell(colIndex).SetCellValue(columnName);
headerRow.GetCell(colIndex).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(colIndex, (arrColWidth[colIndex] + 1) * 500);
colIndex++;
}
}
public void SetUp()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
HSSFSheet sheet = wb.CreateSheet("new sheet") as HSSFSheet;
cell11 = sheet.CreateRow(0).CreateCell(0) as HSSFCell;
cell11.SetCellType(CellType.Formula);
Evaluator = new HSSFFormulaEvaluator(wb);
}
finally
{
//wb.Close();
}
}
/// <summary>
/// 插入数据行
/// </summary>
protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
{
int rowCount = hsRowCount;
int sheetCount = 1;
/* if (hsRowCount > 0)
* {
* sheetCount = hsRowCount + 1;
* }*/
HSSFSheet newsheet = null;
//循环数据源导出数据集
newsheet = (HSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);
CreateHeader(newsheet, excelWorkbook);
if (dtSource != null)
{
foreach (DataRow dr in dtSource.Rows)
{
rowCount++;
//超出10000条数据 创建新的工作簿
if (rowCount == 10000)
{
rowCount = 1;
sheetCount++;
newsheet = (HSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);
CreateHeader(newsheet, excelWorkbook);
}
HSSFRow newRow = (HSSFRow)newsheet.CreateRow(rowCount);
InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook);
}
}
CreateBottom(newsheet, excelWorkbook);
/* for (int i = 0; i < dtSource.Columns.Count; i++)
* {
* newsheet.AutoSizeColumn(i);
* }*/
}
//public static string trdd()
//{
// return "p";
//}
#region wq
public static string WriteData(DataTable dt, string templatefilename, string caption, int startrow, int startcol, string templateFilePath, string tempFilePath)
{
try
{
FileStream file = new FileStream(templateFilePath + templatefilename + ".xls", FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet tworksheet = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
file.Close();
HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style.WrapText = true;
//style.BorderBottom = (CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
//style.BorderLeft = (CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
//style.BorderRight = (CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
//style.BorderTop = (CellBorderType)NPOI.SS.UserModel.BorderStyle.THIN;
HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
for (int i = startrow; i <= dt.Rows.Count + startrow - 1; i++)
{
HSSFRow row = (HSSFRow)tworksheet.CreateRow(i - 1);
for (int j = startcol; j <= dt.Columns.Count + startcol - 1; j++)
{
HSSFCell cell = (HSSFCell)row.CreateCell(j - 1);
cell.SetCellValue(dt.Rows[i - startrow][j - startcol].ToString().Trim());
cell.CellStyle = style;
}
}
FileStream file1 = new FileStream(tempFilePath + templatefilename + ".xls", FileMode.Create);
hssfworkbook.Write(file1);
file1.Close();
return(file1.Name);
}
catch (Exception e)
{
return("");
}
}
public void TestRowHeight()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
Assert.AreEqual(row.Height, sheet.DefaultRowHeight);
Assert.AreEqual(row.RowRecord.BadFontHeight, false);
row.Height = ((short)123);
Assert.AreEqual(row.Height, 123);
Assert.AreEqual(row.RowRecord.BadFontHeight, true);
row.Height = ((short)-1);
Assert.AreEqual(row.Height, sheet.DefaultRowHeight);
Assert.AreEqual(row.RowRecord.BadFontHeight, false);
}
private string ShengchengExcel(List <ShoukuanGridModel> models)
{
string path = Server.MapPath("~/ShoukuanTemplate.xls");
FileStream stream = System.IO.File.OpenRead(path);
HSSFWorkbook workbook = new HSSFWorkbook(stream);
HSSFSheet sheet = workbook.GetSheetAt(0);
int dataRowIndex = 1;
foreach (ShoukuanGridModel model in models)
{
HSSFRow dataRow = sheet.CreateRow(dataRowIndex);
var cell = dataRow.CreateCell(0);
cell.SetCellValue(model.dingdanhao);
cell = dataRow.CreateCell(1);
cell.SetCellValue(model.yewuyuan);
cell = dataRow.CreateCell(2);
cell.SetCellValue(model.kehu);
cell = dataRow.CreateCell(3);
cell.SetCellValue(model.xiadanRiqi.ToString("yyyy-MM-dd"));
cell = dataRow.CreateCell(4);
cell.SetCellValue(model.jiekuanRiqi.ToString("yyyy-MM-dd"));
cell = dataRow.CreateCell(5);
cell.SetCellValue(Math.Round(model.shoukuanJine));
cell = dataRow.CreateCell(6);
cell.SetCellValue(Math.Round(model.ticheng, 2));
dataRowIndex++;
}
string tempPath = Server.MapPath(string.Format("~/Temp/{0}.xls", Guid.NewGuid().ToString()));
if (!Directory.Exists(Path.GetDirectoryName(tempPath)))
{
Directory.CreateDirectory(Path.GetDirectoryName(tempPath));
}
Stream newStream = System.IO.File.Open(tempPath, FileMode.Create);
workbook.Write(newStream);
newStream.Close();
stream.Close();
workbook = null;
sheet = null;
return(Path.GetFileName(tempPath));
}
/// <summary>
/// 將多個DataTable寫入ExcelFile並以Stream輸出.
/// </summary>
/// <param name="templateFileName">範本檔檔名</param>
/// <param name="sourceTables">source table.</param>
/// <param name="startRows">指定由範本檔第幾列開始附加資料</param>
/// <returns></returns>
public static Stream RenderDataTableToExcelSheet(string templateFileName, DataTable[] sourceTables, int[] startRows)
{
HSSFWorkbook workbook = FileToWorkBook(templateFileName);;
MemoryStream ms = new MemoryStream();;
HSSFSheet sheet = null;
for (int idx = 0; idx < sourceTables.Length; idx++)
{
sheet = (HSSFSheet)workbook.GetSheetAt(idx);
DataTable sourceTable = sourceTables[idx];
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyle.BorderTop = cellStyle.BorderLeft;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.WrapText = true;
// handling value.
int rowIndex = startRows[idx];
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(column.Ordinal);
cell.SetCellValue(row[column].ToString());
cell.CellStyle = cellStyle;
}
rowIndex++;
}
sheet = null;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return(ms);
}
private void AddColumnContent(HSSFSheet sheet, int col, List <string> contents)
{
for (int i = 0; i < contents.Count; i++)
{
HSSFRow row;
if (sheet.LastRowNum < i + 1)
{
row = sheet.CreateRow(i + 1) as HSSFRow;
}
else
{
row = sheet.GetRow(i + 1) as HSSFRow;
}
HSSFCell cell = row.CreateCell(col) as HSSFCell;
cell.SetCellValue(new HSSFRichTextString(contents[i]));
}
}
public void TestEvaluate_bug_45732()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Sheet1");
HSSFCell cell = (HSSFCell)sheet.CreateRow(0).CreateCell(0);
cell.CellFormula = ("NPER(12,4500,100000,100000)");
cell.SetCellValue(15.0);
Assert.AreEqual("NPER(12,4500,100000,100000)", cell.CellFormula);
Assert.AreEqual(CellType.NUMERIC, cell.CachedFormulaResultType);
Assert.AreEqual(15.0, cell.NumericCellValue, 0.0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
fe.EvaluateFormulaCell(cell);
Assert.AreEqual(CellType.ERROR, cell.CachedFormulaResultType);
Assert.AreEqual(HSSFErrorConstants.ERROR_NUM, cell.ErrorCellValue);
}
/// <summary>
/// 表头及样式
/// </summary>
/// <param name="headerTextDic"></param>
/// <param name="fileName"></param>
/// <param name="isShowTitle"></param>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
private static void SetDataTitle(Dictionary <string, string> headerTextDic, string fileName, bool isShowTitle, HSSFWorkbook workbook, HSSFSheet sheet)
{
if (isShowTitle)
{
var headerRow = sheet.CreateRow(0) as HSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(fileName);
var headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.CENTER;
var font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, headerTextDic.Count - 1));
}
}
/// <summary>
/// 商户导入商品模板
/// </summary>
public void GoodModel()
{
HSSFWorkbook workbook2007 = new HSSFWorkbook(); //创建xlsx工作簿
workbook2007.CreateSheet("Sheet1"); //新建1个Sheet工作表
//ISheet sheet = workbook2007.CreateSheet("Sheet1");
HSSFSheet SheetOne = (HSSFSheet)workbook2007.GetSheet("Sheet1"); //获取名称为Sheet1的工作表
//对工作表先添加行，下标从0开始
for (int i = 0; i < 2; i++)
{
SheetOne.CreateRow(i); //为SheetOne添加2行
//IRow row = SheetOne.CreateRow(i);
}
//对每一行创建3个单元格
HSSFRow SheetRow = (HSSFRow)SheetOne.GetRow(0); //获取Sheet1工作表的首行
HSSFCell[] SheetCell = new HSSFCell[10];
for (int i = 0; i < 10; i++)
{
SheetCell[i] = (HSSFCell)SheetRow.CreateCell(i); //为第一行创建3个单元格
//ICell headcell = SheetRow.CreateCell(i);
}
//创建之后就可以赋值了
SheetCell[0].SetCellValue("game_id");
SheetCell[1].SetCellValue("service_id");
SheetCell[2].SetCellValue("platform_id");
SheetCell[3].SetCellValue("role_id");
SheetCell[4].SetCellValue("role_name");
SheetCell[5].SetCellValue("title");
SheetCell[6].SetCellValue("brief");
SheetCell[7].SetCellValue("password");
SheetCell[8].SetCellValue("created_at");
SheetCell[9].SetCellValue("price");
//不指定路径
MemoryStream memoryStream = new MemoryStream(); //创建内存流
workbook2007.Write(memoryStream); //npoi将创建好的工作簿写入到内存流
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "123.xls");
HttpContext.Current.Response.BinaryWrite(memoryStream.ToArray());
HttpContext.Current.Response.End();
memoryStream.Dispose();
workbook2007.Close();
}
private void ExportExcel()
{
DataTable dt = svr.SearchByCriteria("vw_CRMCustomer", "CustName,CustFullName,CustType,CommissionFactor", "", "");
//read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "CRM\\Excel\\Product_Template.xls", FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet1 = hssfworkbook.GetSheet("客户信息");
//row,cell都是从0开始计数
//第1行title,不是数据
HSSFCellStyle cellStyle = hssfworkbook.CreateCellStyle();
//- 细边缘
cellStyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
cellStyle.BorderLeft = HSSFCellStyle.BORDER_THIN;
cellStyle.BorderRight = HSSFCellStyle.BORDER_THIN;
cellStyle.BorderTop = HSSFCellStyle.BORDER_THIN;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
HSSFCell cell = sheet1.CreateRow(i + 1).CreateCell(j);
cell.CellStyle = cellStyle;
cell.SetCellValue(dr[j].ToString());
}
}
//Excel文件在被打开的时候自动将焦点定位在单元格
sheet1.GetRow(0).GetCell(0).SetAsActiveCell();
//Force excel to recalculate all the formula while open
sheet1.ForceFormulaRecalculation = true;
hssfworkbook.ActiveSheetIndex = 0;
string FullFileName = AppDomain.CurrentDomain.BaseDirectory + "Upload\\Excel\\Customer_" +
DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString("00") + ".xls";
file = new FileStream(FullFileName, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
DownloadFileAsAttachment(FullFileName);
}
public static void CreatExcel(string fileName)
{
Console.WriteLine("新建Excel文件测试");
Console.ReadKey();
Console.WriteLine("创建中...");
HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建xls工作簿
workbook2003.CreateSheet("Sheet1"); //新建3个Sheet工作表
workbook2003.CreateSheet("Sheet2");
workbook2003.CreateSheet("Sheet3");
HSSFSheet SheetOne = (HSSFSheet)workbook2003.GetSheet("Sheet1"); //获取名称为Sheet1的工作表
for (int i = 0; i < 10; i++)
{
SheetOne.CreateRow(i); //创建10行
}
//对每一行创建10个单元格
HSSFRow SheetRow = (HSSFRow)SheetOne.GetRow(0); //获取Sheet1工作表的首行
HSSFCell[] SheetCell = new HSSFCell[10];
for (int i = 0; i < 10; i++)
{
SheetCell[i] = (HSSFCell)SheetRow.CreateCell(i); //为第一行创建10个单元格
}
//创建之后就可以赋值了
SheetCell[0].SetCellValue(true); //赋值为bool型
SheetCell[1].SetCellValue(0.000001); //赋值为浮点型
SheetCell[2].SetCellValue("Excel2003"); //赋值为字符串
SheetCell[3].SetCellValue("321"); //赋值为长字符串
for (int i = 4; i < 10; i++)
{
SheetCell[i].SetCellValue(i); //循环赋值为整形
}
FileStream file2003 = new FileStream(@fileName, FileMode.Create);
workbook2003.Write(file2003);
file2003.Close(); //关闭文件流
workbook2003.Close();
Console.WriteLine("创建成功" + fileName);
Console.ReadKey();
}
public void TestDateWithNegativeParts_bug48528()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Sheet1");
HSSFRow row = (HSSFRow)sheet.CreateRow(1);
HSSFCell cell = (HSSFCell)row.CreateCell(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
// 5th Feb 2012 = 40944
// 1st Feb 2012 = 40940
// 5th Jan 2012 = 40913
// 5th Dec 2011 = 40882
// 5th Feb 2011 = 40579
cell.CellFormula = ("DATE(2012,2,1)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40940.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula = ("DATE(2012,2,1+4)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40944.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula = ("DATE(2012,2-1,1+4)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40913.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula = ("DATE(2012,2,1-27)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40913.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula = ("DATE(2012,2-2,1+4)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40882.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula = ("DATE(2012,2,1-58)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40882.0, fe.Evaluate(cell).NumberValue);
cell.CellFormula = ("DATE(2012,2-12,1+4)");
fe.NotifyUpdateCell(cell);
Assert.AreEqual(40579.0, fe.Evaluate(cell).NumberValue);
wb.Close();
}
public void TestWriteModifySheetSimple()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet s = wb1.CreateSheet() as HSSFSheet;
for (int rownum = 0; rownum < 100; rownum++)
{
HSSFRow r = s.CreateRow(rownum) as HSSFRow;
for (int cellnum = 0; cellnum < 50; cellnum += 2)
{
HSSFCell c = r.CreateCell(cellnum) as HSSFCell;
c.SetCellValue(rownum * 10000 + cellnum
+ (((double)rownum / 1000)
+ ((double)cellnum / 10000)));
c = r.CreateCell(cellnum + 1) as HSSFCell;
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
for (int rownum = 0; rownum < 25; rownum++)
{
HSSFRow r = s.GetRow(rownum) as HSSFRow;
s.RemoveRow(r);
}
for (int rownum = 75; rownum < 100; rownum++)
{
HSSFRow r = s.GetRow(rownum) as HSSFRow;
s.RemoveRow(r);
}
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
sanityChecker.CheckHSSFWorkbook(wb1);
Assert.AreEqual(74, s.LastRowNum, "LAST ROW == 74");
Assert.AreEqual(25, s.FirstRowNum, "FIRST ROW == 25");
s = wb2.GetSheetAt(0) as HSSFSheet;
Assert.AreEqual(74, s.LastRowNum, "LAST ROW == 74");
Assert.AreEqual(25, s.FirstRowNum, "FIRST ROW == 25");
wb2.Close();
wb1.Close();
}
public void TestOptimiseStylesCheckActualStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Several styles
Assert.AreEqual(21, wb.NumCellStyles);
HSSFCellStyle cs1 = (HSSFCellStyle)wb.CreateCellStyle();
cs1.BorderBottom = (BorderStyle.Thick);
HSSFCellStyle cs2 = (HSSFCellStyle)wb.CreateCellStyle();
cs2.BorderBottom = (BorderStyle.DashDot);
HSSFCellStyle cs3 = (HSSFCellStyle)wb.CreateCellStyle(); // = cs1
cs3.BorderBottom = (BorderStyle.Thick);
Assert.AreEqual(24, wb.NumCellStyles);
// Use them
HSSFSheet s = (HSSFSheet)wb.CreateSheet();
HSSFRow r = (HSSFRow)s.CreateRow(0);
r.CreateCell(0).CellStyle = (cs1);
r.CreateCell(1).CellStyle = (cs2);
r.CreateCell(2).CellStyle = (cs3);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
Assert.AreEqual(23, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
// Optimise
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(23, wb.NumCellStyles);
Assert.AreEqual(BorderStyle.Thick, r.GetCell(0).CellStyle.BorderBottom);
Assert.AreEqual(BorderStyle.DashDot, r.GetCell(1).CellStyle.BorderBottom);
Assert.AreEqual(BorderStyle.Thick, r.GetCell(2).CellStyle.BorderBottom);
}
private void AddTitle(HSSFSheet sheet, int col, string content)
{
HSSFRow row = col == 0 ? sheet.CreateRow(0) as HSSFRow : sheet.GetRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(col) as HSSFCell;
cell.SetCellValue(new HSSFRichTextString(content));
CellStyle style = sheet.Workbook.CreateCellStyle();
Font font = sheet.Workbook.CreateFont();
font.Color = HSSFColor.LIGHT_BLUE.index;
font.Boldweight = (short)700;
style.Alignment = HorizontalAlignment.CENTER;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.SetFont(font);
cell.CellStyle = style;
sheet.SetColumnWidth(col, mDynameter * 70);
}
/// <summary>逐列進行資料列的初始化</summary>
/// <param name="sheet">要設定的工作表 (Sheet)</param>
private void SetDataRowDefault(HSSFSheet sheet)
{
HSSFRow _row;
float _defaultRowHeightInPoints;
_defaultRowHeightInPoints = sheet.DefaultRowHeightInPoints;
for (int rowIndex = 0; rowIndex < sheet.LastRowNum; rowIndex++)
{
_row = sheet.GetRow(rowIndex) as HSSFRow;
if (_row == null)
{
_row = sheet.CreateRow(rowIndex) as HSSFRow;
_row.HeightInPoints = _defaultRowHeightInPoints;
_row.CreateCell(0).SetCellValue(string.Empty);
}
}
}
public void Main()
{
Console.Read();
InitializeWorkbook();
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1");
ICell cell1 = sheet1.CreateRow(0).CreateCell(0);
cell1.SetCellValue("This is a Sample");
ICellStyle cs1 = hssfworkbook.CreateCellStyle();
cs1.IsLocked = true;
cell1.CellStyle = cs1;
sheet1.ProtectSheet("test");
WriteToFile();
}
/*
* public IList<Items> SearchCommon(Items o, ViewDataDictionary v, string pageModelViewData, string listViewData)
* {
* Type t = o.GetType();
*
* int pDot = t.Namespace.LastIndexOf(".");
* int pLen = t.Namespace.Length;
*
* string dir = (pDot > 1 && (pDot < (pLen - 1))) ? (t.Namespace.Substring(pDot + 1, pLen - pDot - 1) + "/") : "";
*
* string qCount = dir + t.Name + "_Count";
* string qList = dir + t.Name + "_List";
*
* IDBContext db = BaseRepo.Db;
*
* long count = db.SingleOrDefault<int>(qCount, o);
*
* BaseModel m = o as BaseModel;
* PagingModel pmodel = null;
* if (m.RowsPerPage <= 0)
* {
* pmodel = new PagingModel(count, 1, PagingModel.DEFAULT_RECORD_PER_PAGE);
* }
* else
* {
* pmodel = new PagingModel(count, m.CurrentPage, m.RowsPerPage);
* }
*
* v[pageModelViewData] = pmodel;
*
* IList<Items> l = db.Fetch<Items>(qList, o);
*
* return l;
* }
*/
#endregion
#region DownloadTemplate
public void DownloadTemplate()
{
string fileName = "DownloadTemplateItemToUpload.xls";
string filesTMp = HttpContext.Request.MapPath("~" +
CommonConstant.TEMPLATE_EXCEL_DIR + "/" + fileName);
FileStream ftmp = new FileStream(filesTMp, FileMode.Open, FileAccess.Read);
byte[] result = null;
HSSFWorkbook workbook = new HSSFWorkbook(ftmp);
ICellStyle cellStyleData = NPOIWriter.createCellStyleData(workbook);
//ICellStyle cellStyleHeader = NPOIWriter.createCellStyleColumnHeader(workbook);
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
int row = DATA_ROW_INDEX_START;
IRow Hrow;
for (int i = 1; i <= 100; i++)
{
Hrow = sheet.CreateRow(row);
for (int x = 0; x <= 2; x++)
{
Hrow.CreateCell(x);
Hrow.GetCell(x).CellStyle = cellStyleData;
}
row++;
}
int rowMin = row - 1;
ftmp.Close();
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
result = ms.GetBuffer();
}
this.SendDataAsAttachment(fileName, result);
}
/*
*
* public void ListToExcelByNPOI(List<T> data)
* {
* string pasthname = "结果-" + DateTime.Now.ToString("yyyy-MM-dd") + "导出" + ".xls";
* HSSFWorkbook workbook = new HSSFWorkbook();
* ISheet sheet = workbook.CreateSheet("Sheet1");
* //获取公共属性由于做表头
* var propertys = typeof(UploadTestResultInfos).GetProperties();
* List<string> title = new List<string>();
* foreach (PropertyInfo item in typeof(UploadTestResultInfos).GetProperties())
* {
* //if (!Ignore.IgnoreField(item.Name))
* // continue;
*
* title.Add(item.Name);
* }
*
* var rowtitle = sheet.CreateRow(0);
*
* for (var i = 0; i < title.Count; i++)
* {
* rowtitle.CreateCell(i).SetCellValue(title[i]);
* }
*
*
* for (var i = 0; i < data.Count; i++)
* {
* var row = sheet.CreateRow(i + 1); //因为表头名称占了一行，所以加1
* for (var j = 0; j < propertys.Length; j++)
* {
* //if (!Ignore.IgnoreField(propertys[j].Name))
* // continue;
*
* var obj = propertys[j].GetValue(data[i], null);
* row.CreateCell(j).SetCellValue(obj.ToString().Trim());
* }
* }
*
* using (MemoryStream ms = new MemoryStream())
* {
* workbook.Write(ms);
* //Web导出
* HttpContext curContext = HttpContext.Current;
* curContext.Response.ContentType = "application/vnd.ms-excel";
* curContext.Response.ContentEncoding = Encoding.UTF8;
* curContext.Response.Charset = "";
* curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(pasthname, Encoding.UTF8));
* curContext.Response.BinaryWrite(ms.GetBuffer());
* curContext.Response.End();
* }
* }*/
protected void Button1_Click(object sender, EventArgs e)
{
HSSFWorkbook workbook2007 = new HSSFWorkbook(); //创建xlsx工作簿
workbook2007.CreateSheet("Sheet1"); //新建1个Sheet工作表
//ISheet sheet = workbook2007.CreateSheet("Sheet1");
HSSFSheet SheetOne = (HSSFSheet)workbook2007.GetSheet("Sheet1"); //获取名称为Sheet1的工作表
//对工作表先添加行，下标从0开始
for (int i = 0; i < 2; i++)
{
SheetOne.CreateRow(i); //为SheetOne添加2行
//IRow row = SheetOne.CreateRow(i);
}
//对每一行创建3个单元格
HSSFRow SheetRow = (HSSFRow)SheetOne.GetRow(0); //获取Sheet1工作表的首行
HSSFCell[] SheetCell = new HSSFCell[4];
for (int i = 0; i < 4; i++)
{
SheetCell[i] = (HSSFCell)SheetRow.CreateCell(i); //为第一行创建3个单元格
//ICell headcell = SheetRow.CreateCell(i);
}
//创建之后就可以赋值了
SheetCell[0].SetCellValue("CellValue");
SheetCell[1].SetCellValue("CellValue2");
SheetCell[2].SetCellValue("CellValue3");
SheetCell[3].SetCellValue("CellValue4");
//不指定路径
MemoryStream memoryStream = new MemoryStream(); //创建内存流
workbook2007.Write(memoryStream); //npoi将创建好的工作簿写入到内存流
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "123.xls");
HttpContext.Current.Response.BinaryWrite(memoryStream.ToArray());
HttpContext.Current.Response.End();
memoryStream.Dispose();
workbook2007.Close();
//指定路径保存
//FileStream file2007 = new FileStream(@"E:\Excel2007.xls", FileMode.Create);
//workbook2007.Write(file2007);
//file2007.Close();
//workbook2007.Close();
}
public void TestCellType()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(0) as HSSFCell;
cell.SetCellType(CellType.Blank);
Assert.AreEqual("9999-12-31 23:59:59.999", cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff"));
Assert.IsFalse(cell.BooleanCellValue);
Assert.AreEqual("", cell.ToString());
cell.SetCellType(CellType.String);
Assert.AreEqual("", cell.ToString());
cell.SetCellType(CellType.String);
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellType(CellType.Boolean);
Assert.AreEqual("TRUE", cell.ToString());
cell.SetCellType(CellType.Boolean);
cell.SetCellValue("" + FormulaError.VALUE.String);
cell.SetCellType(CellType.Error);
Assert.AreEqual("#VALUE!", cell.ToString());
cell.SetCellType(CellType.Error);
cell.SetCellType(CellType.Boolean);
Assert.AreEqual("FALSE", cell.ToString());
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellType(CellType.Boolean);
cell.SetCellType(CellType.String);
cell.SetCellType(CellType.Error);
cell.SetCellType(CellType.String);
cell.SetCellValue(1.2);
cell.SetCellType(CellType.Numeric);
cell.SetCellType(CellType.String);
Assert.AreEqual("1.2", cell.ToString());
cell.SetCellValue((string)null);
cell.SetCellValue((IRichTextString)null);
wb.Close();
}
/// <summary>
/// 增加表头信息
/// </summary>
/// <param name="sheet">当前sheet</param>
/// <param name="rowIndex">行索引</param>
/// <param name="row">填充的数据</param>
/// <param name="indentationIndex">缩进</param>
/// <param name="hidden"></param>
/// <param name="headerCellStyle"></param>
public static void PrintHead(HSSFSheet sheet, ref int rowIndex, DataRow row, int indentationIndex,
Hashtable hidden, ICellStyle headerCellStyle)
{
int left = 0;
var headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
headerCellStyle.Alignment = HorizontalAlignment.Center;
headerCellStyle.FillForegroundColor = HSSFColor.SkyBlue.Index;
headerCellStyle.FillPattern = FillPattern.SolidForeground;
headerCellStyle.VerticalAlignment = VerticalAlignment.Center;
headerCellStyle.Alignment = HorizontalAlignment.Center;
string[] hidenColmunsName;
if (hidden == null)
{
hidenColmunsName = null;
}
else
{
try
{
hidenColmunsName = (string[])hidden[row.Table.TableName];
}
catch
{
hidenColmunsName = null;
}
}
for (int i = 0; i < row.Table.Columns.Count; i++)
{
if (hidenColmunsName == null)
{
}
else if (hidenColmunsName.Contains(row.Table.Columns[i].ColumnName))
{
left++;
continue;
}
SetCell(headerRow, headerCellStyle, i + indentationIndex - left, row.Table.Columns[i].ColumnName);
}
rowIndex++;
}
private void CellDataWriterFirstAndOthers(int row, int col, DataTable dt, string FilePath, string SheetName)
{
FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.ReadWrite);
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);
HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet(SheetName);
fs.Close();
int i = 0;
int r = row;
foreach (DataRow dr in dt.Rows)
{
HSSFRow headerRow4 = (HSSFRow)sheet.CreateRow(r);
int j = 0;
int c = col;
foreach (DataColumn dc in dt.Columns)
{
HSSFCell cell1 = (HSSFCell)headerRow4.CreateCell(c);
string value = dt.Rows[i][j].ToString();
if (value == "0" || value == "0.00")
{
value = string.Empty;
}
sheet.GetRow(r).GetCell(c).SetCellValue(value);
j++;
c++;
}
i++;
r++;
}
fs = new FileStream(FilePath, FileMode.Open, FileAccess.ReadWrite);
templateWorkbook.Write(fs);
fs.Close();
dt.Columns.Clear();
dt.Rows.Clear();
}
public void TestSetMargins_bug45717()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Vorschauliste");
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
sheet.SetMargin(Npoi.Core.SS.UserModel.MarginType.LeftMargin, 0.3);
try
{
row.CreateCell(0);
}
catch (InvalidOperationException e)
{
if (e.Message.Equals("Cannot Create value records before row records exist"))
{
throw new AssertionException("Identified bug 45717");
}
throw e;
}
}
protected override void WriteCell(int Column, int Row, string WorksheetName, object Value)
{
Type valueType = Value.GetType();
HSSFSheet worksheet = VerifyWorksheet(WorksheetName);
HSSFRow wsRow = (HSSFRow)worksheet.GetRow(Row) ?? (HSSFRow)worksheet.CreateRow(Row);
HSSFCell cell = (HSSFCell)wsRow.CreateCell(Column);
if (valueType == typeof(DateTime))
{
WriteCellTypeValue(Convert.ToDateTime(Value), cell);
}
else if (valueType == typeof(Double) || valueType == typeof(Decimal))
{
WriteCellTypeValue(Convert.ToDouble(Value), cell);
}
else
{
WriteCellTypeValue(Value.ToString(), cell);
}
}
public void TestBasicCopyTo()
{
HSSFWorkbook bookA = new HSSFWorkbook();
HSSFWorkbook bookB = new HSSFWorkbook();
HSSFWorkbook bookC = new HSSFWorkbook();
HSSFSheet sheetA = bookA.CreateSheet("Sheet A") as HSSFSheet;
sheetA.CreateRow(0).CreateCell(0).SetCellValue("Data in the first book");
HSSFSheet sheetB = bookB.CreateSheet("Sheet B") as HSSFSheet;
sheetB.CreateRow(0).CreateCell(0).SetCellValue("Data in the second book");
//Ensure that we can copy into a book that already has a sheet, as well as one that doesn't.
sheetA.CopyTo(bookB, "Copied Sheet A", false, false);
sheetA.CopyTo(bookC, "Copied Sheet A", false, false);
//Ensure the sheet was copied to the 2nd sheet of Book B, not the 1st sheet.
Assert.AreNotEqual(sheetA.GetRow(0).GetCell(0).StringCellValue, bookB.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
Assert.AreEqual(sheetA.GetRow(0).GetCell(0).StringCellValue, bookB.GetSheetAt(1).GetRow(0).GetCell(0).StringCellValue);
//Ensure the sheet was copied to the 1st sheet in Book C
Assert.AreEqual(sheetA.GetRow(0).GetCell(0).StringCellValue, bookC.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
}
