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 void TestCountifExamples()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("countifExamples.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFSheet sheet1 = (HSSFSheet)wb.GetSheet("MSDN Example 1");
for (int rowIx = 7; rowIx <= 12; rowIx++)
{
HSSFRow row = (HSSFRow)sheet1.GetRow(rowIx - 1);
HSSFCell cellA = (HSSFCell)row.GetCell(0); // cell containing a formula with COUNTIF
Assert.AreEqual(CellType.Formula, cellA.CellType);
HSSFCell cellC = (HSSFCell)row.GetCell(2); // cell with a reference value
Assert.AreEqual(CellType.Numeric, cellC.CellType);
CellValue cv = fe.Evaluate(cellA);
double actualValue = cv.NumberValue;
double expectedValue = cellC.NumericCellValue;
Assert.AreEqual(expectedValue, actualValue, 0.0001,
"Problem with a formula at " + new CellReference(cellA).FormatAsString()
+ ": " + cellA.CellFormula + " :"
+ "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
}
HSSFSheet sheet2 = (HSSFSheet)wb.GetSheet("MSDN Example 2");
for (int rowIx = 9; rowIx <= 14; rowIx++)
{
HSSFRow row = (HSSFRow)sheet2.GetRow(rowIx - 1);
HSSFCell cellA = (HSSFCell)row.GetCell(0); // cell containing a formula with COUNTIF
Assert.AreEqual(CellType.Formula, cellA.CellType);
HSSFCell cellC = (HSSFCell)row.GetCell(2); // cell with a reference value
Assert.AreEqual(CellType.Numeric, cellC.CellType);
CellValue cv = fe.Evaluate(cellA);
double actualValue = cv.NumberValue;
double expectedValue = cellC.NumericCellValue;
Assert.AreEqual(expectedValue, actualValue, 0.0001,
"Problem with a formula at " +
new CellReference(cellA).FormatAsString() + "[" + cellA.CellFormula + "]: "
+ "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
}
}
/// <summary>
/// 导出Excel并每6万条打包
/// </summary>
/// <param name="dsSource"></param>
/// <param name="excelStream"></param>
/// <param name="SheetName"></param>
public static void ExportExcelZip(DataTable dt, string SheetName, string tmpPath)
{
//每个文件条数
int num = 60000;
if (dt == null || SheetName == null)
{
return;
}
int count = (int)Math.Ceiling(dt.Rows.Count * 1.0 / num);
for (int i = 0; i < count; i++)
{
int rowCount = 0;
int cellIndex = 0;
HSSFWorkbook excelWorkbook = CreateExcelFile();
HSSFSheet newsheet = null;
//循环数据源导出数据集
newsheet = excelWorkbook.CreateSheet(SheetName);
//创建标题
cellIndex = 0;
StarTech.NPOI.NPOIHelper.ListColumnsName = new SortedList(new StarTech.NPOI.NoSort());
//循环导出列
foreach (DataColumn dc in dt.Columns)
{
HSSFRow newRow = newsheet.CreateRow(0);
HSSFCell newCell = newRow.CreateCell(cellIndex);
newCell.SetCellValue(dc.ColumnName);
StarTech.NPOI.NPOIHelper.ListColumnsName.Add(dc.ColumnName, dc.ColumnName);
cellIndex++;
}
rowCount = 0;
for (int j = i * num; (j < (i + 1) * num) && (j < dt.Rows.Count); j++)
{
DataRow dr = dt.Rows[j];
++rowCount;
HSSFRow newRow = newsheet.CreateRow(rowCount);
InsertCell(dt, dr, newRow, newsheet, excelWorkbook);
}
SaveExcelFile(excelWorkbook, tmpPath + "/" + (i + 1).ToString() + ".xls");
}
//压缩文件夹
Compress.ZipCompress(tmpPath, tmpPath + ".zip");
}
protected static void FormServiceStatesRender(HSSFCell cell, string varName, DataSet dataset, object value, string emptyText)
{
string strValue = Convert.ToString(value);
string rv = null;
if (!String.IsNullOrEmpty(strValue))
{
JArray array = JArray.Parse(strValue);
BPMObjectNameCollection names = new BPMObjectNameCollection();
foreach (JObject item in array)
{
names.Add((string)(item["Name"]));
}
rv = String.Join(",", names.ToArray());
}
DefaultRender(cell, varName, dataset, rv, String.Empty);
}
/// <summary>
/// 导入Excel
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static DataTable Upload2DataTable(string path)
{
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
//创建列名，以第一行为名
if (rows.MoveNext())
{
HSSFRow row = rows.Current as HSSFRow;
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = row.GetCell(i) as HSSFCell;
if (cell == null)
{
dt.Columns.Add("无效");
}
else
{
dt.Columns.Add(cell.ToString());
}
}
}
//数据从第二行开始
while (rows.MoveNext())
{
HSSFRow row = rows.Current as HSSFRow;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
GetCellValue(row, dr, i);
}
dt.Rows.Add(dr);
}
return(dt);
}
}
public void TestFromFile()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("sumifs.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFSheet example1 = (HSSFSheet)wb.GetSheet("Example 1");
HSSFCell ex1cell1 = (HSSFCell)example1.GetRow(10).GetCell(2);
fe.Evaluate(ex1cell1);
Assert.AreEqual(20.0, ex1cell1.NumericCellValue);
HSSFCell ex1cell2 = (HSSFCell)example1.GetRow(11).GetCell(2);
fe.Evaluate(ex1cell2);
Assert.AreEqual(30.0, ex1cell2.NumericCellValue);
HSSFSheet example2 = (HSSFSheet)wb.GetSheet("Example 2");
HSSFCell ex2cell1 = (HSSFCell)example2.GetRow(6).GetCell(2);
fe.Evaluate(ex2cell1);
Assert.AreEqual(500.0, ex2cell1.NumericCellValue);
HSSFCell ex2cell2 = (HSSFCell)example2.GetRow(7).GetCell(2);
fe.Evaluate(ex2cell2);
Assert.AreEqual(8711.0, ex2cell2.NumericCellValue);
HSSFSheet example3 = (HSSFSheet)wb.GetSheet("Example 3");
HSSFCell ex3cell = (HSSFCell)example3.GetRow(5).GetCell(2);
fe.Evaluate(ex3cell);
Assert.AreEqual(8, 8, ex3cell.NumericCellValue);
HSSFSheet example4 = (HSSFSheet)wb.GetSheet("Example 4");
HSSFCell ex4cell = (HSSFCell)example4.GetRow(8).GetCell(2);
fe.Evaluate(ex4cell);
Assert.AreEqual(3.5, ex4cell.NumericCellValue);
HSSFSheet example5 = (HSSFSheet)wb.GetSheet("Example 5");
HSSFCell ex5cell = (HSSFCell)example5.GetRow(8).GetCell(2);
fe.Evaluate(ex5cell);
Assert.AreEqual(625000.0, ex5cell.NumericCellValue);
}
/// <summary>
/// 设置底边框样式
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="borderStyle"></param>
/// <param name="wb"></param>
public static void SetCellBorderBottom(int row, int col, NPOI.SS.UserModel.BorderStyle borderStyle, ref HSSFWorkbook wb)
{
ICellStyle cellStyle = wb.CreateCellStyle();
ISheet sheet = wb.GetSheetAt(0);
if (sheet.GetRow(row - 1) == null)
{
HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
cellStyle.BorderBottom = borderStyle;
t_cell.CellStyle = cellStyle;
}
else
{
HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);
if (t_row.GetCell(col - 1) == null)
{
HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
cellStyle.BorderBottom = borderStyle;
t_cell.CellStyle = cellStyle;
}
else
{
HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);
cellStyle.CloneStyleFrom(t_cell.CellStyle);
cellStyle.BorderBottom = borderStyle;
t_cell.CellStyle = cellStyle;
}
}
}
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>
/// 读取2007以上版本.xlsx
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string Read2003ToString(string path)
{
HSSFWorkbook hssfworkbook;
path = HttpContext.Current.Server.MapPath(path);
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
StringBuilder sb = new StringBuilder();
int irow = 0;
sb.Append("<table>");
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
irow++;
sb.Append("<tr>");
for (int i = 0; i < row.LastCellNum; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
string dr = "";
if (cell == null)
{
dr = "";
}
else
{
dr = cell.ToString();
}
sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
}
sb.Append("</tr>");
}
sb.Append("</table>");
return(sb.ToString());
}
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 AddCurrencyHeaderCellLedger(decimal amount, int index, bool isDoubleLine, HorizontalAlignment xAlign = HorizontalAlignment.RIGHT)
{
columnIndex = index + offset;
HSSFCellStyle style = HeaderCellStyle(xAlign); //GetCurrencyStyleFormat();
style.DataFormat = df.GetFormat("$#,##0.00");
if (isDoubleLine)
{
style.BorderBottom = CellBorderType.DOUBLE;
style.BottomBorderColor = HSSFColor.WHITE.index;
}
HSSFCell cell = (HSSFCell)_HSSFCurrentRow.CreateCell(columnIndex);
cell.SetCellValue(Convert.ToDouble(amount));
cell.CellStyle = style;
ManualAdjustColumnWidth(Convert.ToString(amount));
columnIndex++;
}
/*
*
* 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 TestMissingWorkbookMissing()
{
IFormulaEvaluator evaluator = mainWorkbook.GetCreationHelper().CreateFormulaEvaluator();
HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
HSSFRow lARow = (HSSFRow)lSheet.GetRow(0);
HSSFCell lA1Cell = (HSSFCell)lARow.GetCell(0);
Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
try
{
evaluator.EvaluateFormulaCell(lA1Cell);
Assert.Fail("Missing external workbook reference exception expected!");
}
catch (RuntimeException re)
{
Assert.IsTrue(re.Message.IndexOf(SOURCE_DUMMY_WORKBOOK_FILENAME) != -1, "Unexpected exception: " + re);
}
}
public void AddCurrencyCell(decimal amount, NPOI.SS.UserModel.HorizontalAlignment xAlign = NPOI.SS.UserModel.HorizontalAlignment.GENERAL)
{
HSSFCell cell = (HSSFCell)_HSSFCurrentRow.CreateCell(columnIndex);
cell.SetCellValue(Convert.ToDouble(amount));
cell.CellStyle = GetCurrencyStyleFormat();
cell.CellStyle.BorderLeft = CellBorderType.NONE;
cell.CellStyle.BorderRight = CellBorderType.NONE;
ManualAdjustColumnWidth(Convert.ToString(amount));
if (xAlign != HorizontalAlignment.GENERAL)
{
cell.CellStyle.Alignment = xAlign;
}
else
{
cell.CellStyle.Alignment = HorizontalAlignment.RIGHT;
}
columnIndex++;
}
/// <summary>
/// Function to set a hyperlink in the cell identified by the specified row and column numbers
/// </summary>
/// <param name="rowNum">The row number of the cell</param>
/// <param name="columnNum">The column number of the cell</param>
/// <param name="linkAddress">The link address to be set
public void SetHyperlink(int rowNum, int columnNum, String linkAddress)
{
CheckPreRequisites();
HSSFWorkbook workbook = OpenFileForReading();
HSSFSheet worksheet = GetWorkSheet(workbook);
HSSFRow row = (HSSFRow)worksheet.GetRow(rowNum);
HSSFCell cell = (HSSFCell)row.GetCell(columnNum);
if (cell == null)
{
throw new FrameworkException("Specified cell is empty! Please set a value before including a hyperlink...");
}
SetCellHyperlink(workbook, cell, linkAddress);
WriteIntoFile(workbook);
}
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();
}
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 String getStringCellValue(int row, int col)
{
String sCellValue = "";
try
{
if (this._CurrentSheet.GetRow(row) != null && this._CurrentSheet.GetRow(row).GetCell(col) != null)
{
switch (this._CurrentSheet.GetRow(row).GetCell(col).CellType)
{
case 0:
sCellValue = this._CurrentSheet.GetRow(row).GetCell(col).NumericCellValue.ToString();
break;
case 2:
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(this.book);
HSSFCell cell = this._CurrentSheet.GetRow(row).GetCell(col);
evaluator.EvaluateFormulaCell(cell);
HSSFFormulaEvaluator.CellValue cellValue = evaluator.Evaluate(cell);
Double numericCellValue = cellValue.NumberValue;
if (cellValue.StringValue != null && cellValue.StringValue != "")
{
sCellValue = cellValue.StringValue;
}
else
{
sCellValue = numericCellValue.ToString("N2");
}
break;
default:
sCellValue = this._CurrentSheet.GetRow(row).GetCell(col).StringCellValue;
break;
}
}
}
catch (System.Exception ex)
{
Logger.Instance.LogMessage(Logger.typeLogMessage.CRITICAL, Logger.typeUserActions.SELECT, -1, ex.Message, "ExcelFileReader");
}
return(sCellValue);
}
/// <summary>取得指定儲存格的文字內容</summary>
/// <param name="cell">指定儲存格</param>
private string GetStringValue(HSSFCell cell)
{
/*
* 數字格式將轉換成文字 1 => "1"
* 其餘格式顯示 string.Empty
*/
string _value;
_value = string.Empty;
switch (cell.CellType)
{
case CellType.Blank:
break;
case CellType.Boolean:
break;
case CellType.Error:
break;
case CellType.Formula:
break;
case CellType.Numeric:
_value = cell.NumericCellValue.ToString();
break;
case CellType.String:
_value = cell.StringCellValue;
break;
case CellType.Unknown:
break;
default:
break;
}
return(_value);
}
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);
}
public void TestRecalculateFormulas47747()
{
/*
* ex47747-sharedFormula.xls is a heavily cut-down version of the spreadsheet from
* the attachment (id=24176) in Bugzilla 47747. This was done to make the sample
* file smaller, which hopefully allows the special data encoding condition to be
* seen more easily. Care must be taken when modifying this file since the
* special conditions are easily destroyed (which would make this test useless).
* It seems that removing the worksheet protection has made this more so - if the
* current file is re-saved in Excel(2007) the bug condition disappears.
*
*
* Using BiffViewer, one can see that there are two shared formula groups representing
* the essentially same formula over ~20 cells. The shared group ranges overlap and
* are A12:Q20 and A20:Q27. The locator cell ('first cell') for the second group is
* Q20 which is not the top left cell of the enclosing range. It is this specific
* condition which caused the bug to occur
*/
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ex47747-sharedFormula.xls");
// pick out a cell from within the second shared formula group
HSSFCell cell = (HSSFCell)wb.GetSheetAt(0).GetRow(23).GetCell(0);
string formulaText;
try
{
formulaText = cell.CellFormula;
// succeeds if the formula record has been associated
// with the second shared formula group
}
catch (RuntimeException e)
{
// bug occurs if the formula record has been associated
// with the first shared formula group
if ("Shared Formula Conversion: Coding Error".Equals(e.Message))
{
throw new AssertionException("Identified bug 47747");
}
throw e;
}
Assert.AreEqual("$AF24*A$7", formulaText);
}
public void InsertPageBreaks(HSSFWorkbook workbook, string shtName)
{
//Find instances of the word "pagebreak" and replace with a page break
HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(shtName);
int cellctr = 0;
HSSFCell range = (HSSFCell)sheet.GetRow(cellctr).GetCell(0);
while (range == null || range.ToString() != "EOF")
{
if (range != null && range.ToString() == "pagebreak")
{
sheet.SetRowBreak(cellctr);
range.SetCellValue(string.Empty);
}
cellctr++;
range = (HSSFCell)sheet.GetRow(cellctr).GetCell(0);
}
range.SetCellValue(string.Empty);
range = (HSSFCell)sheet.GetRow(0).GetCell(0);
}
public HSSFCell GetCell(int rowIndex, int columnIndex, bool isNew = false)
{
HSSFCell ret = null;
try
{
ret = NPOIHssfEx.GetCell(m_sheet, rowIndex, columnIndex);
}
catch (System.Exception ex)
{
Debug.LogError("rIndex = " + rowIndex + ",cIndex = " + columnIndex + "\n" + ex);
}
if (isNew && ret == null)
{
HSSFRow row = GetRow(rowIndex, isNew);
ret = NPOIHssfEx.CreateCell(row, columnIndex);
}
return(ret);
}
public static DateTime?GetDateCellValue(this HSSFSheet sheet, int row, string AlphaBet)
{
HSSFCell cell = sheet.GetRow(row).GetCell(Col(AlphaBet));
if (cell == null)
{
return(new DateTime(1900, 1, 1));
}
else
{
if (cell.StringCellValue == "")
{
return(null);
}
else
{
return(cell.DateCellValue);
}
}
}
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);
}
}
protected static void DoRender(string RenderFunction, HSSFCell cell, string varName, DataSet dataset, object value, string emptyText)
{
MethodInfo method = typeof(YZExcelGenerate).GetMethod(RenderFunction, BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.Public);
if (method == null)
{
DefaultRender(cell, varName, dataset, String.Format("{0} not found", RenderFunction), emptyText);
}
else
{
try
{
method.Invoke(null, new object[] { cell, varName, dataset, value, emptyText });
}
catch (Exception e)
{
DefaultRender(cell, varName, dataset, String.Format("{0} : {1}", RenderFunction, e.InnerException.Message), emptyText);
}
}
}
public void FillDataIntoRow(HSSFRow row)
{
int i = 0;
HSSFCell cell = (HSSFCell)row.CreateCell(i);
cell.SetCellValue(Description);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(combinedGroup);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(@group);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(rows);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(users);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(machines);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(F);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(H);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(IP);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(LicenseHash);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(MachineId);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(Version);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(Location);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(Userid);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(count);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(SerialNumber);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(LicenseType);
cell = (HSSFCell)row.CreateCell(++i);
cell.SetCellValue(ShortVer);
}
/// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(HSSFCell cell)
{
if (cell == null)
{
return(null);
}
if (datetimeFormats.IndexOf(cell.CellStyle.GetDataFormatString()) > -1)
{
try
{
return(cell.DateCellValue);
}
catch { }
}
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.BLANK:
return(null);
case NPOI.SS.UserModel.CellType.BOOLEAN:
return(cell.BooleanCellValue);
case NPOI.SS.UserModel.CellType.ERROR:
return(cell.ErrorCellValue);
case NPOI.SS.UserModel.CellType.NUMERIC:
return(cell.NumericCellValue);
case NPOI.SS.UserModel.CellType.STRING:
return(cell.StringCellValue);
case NPOI.SS.UserModel.CellType.Unknown:
return(null);
case NPOI.SS.UserModel.CellType.FORMULA:
default:
return("=" + cell.CellFormula);
}
}
public System.Data.DataTable ReadExcelToDataTable(string filePath)
{
//打开要读取的Excel
FileStream file = new FileStream(filePath, FileMode.Open);
//读入Excel
HSSFWorkbook workbook = new HSSFWorkbook(file);
file.Close();
HSSFSheet sheet = workbook.GetSheetAt(0);
//建立一个新的table
DataTable dtNew = new DataTable();;
HSSFRow row = sheet.GetRow(0);
//读取取第0列作为column name
for (int columnIndex = 0; columnIndex < row.LastCellNum; columnIndex++)
{
DataColumn dc = new DataColumn(row.GetCell(columnIndex).ToString());
dtNew.Columns.Add(dc);
}
int rowId = 1;
//第一列以后为资料，一直读到最后一行
while (rowId <= sheet.LastRowNum)
{
DataRow newRow = dtNew.NewRow();
//读取所有column
for (int colIndex = 0; colIndex < dtNew.Columns.Count; colIndex++)
{
string str = string.Empty;
HSSFCell CellVal = sheet.GetRow(rowId).GetCell(colIndex);
if (CellVal != null)
{
str = CellVal.ToString();
}
newRow[dtNew.Columns[colIndex]] = str;
}
dtNew.Rows.Add(newRow);
rowId++;
}
return(dtNew);
}
/// <summary>
/// Function to set a hyperlink in the cell identified by the specified row number and column header
/// </summary>
/// <param name="rowNum"> The row number of the cell</param>
/// <param name="columnHeader">The column header of the cell</param>
/// <param name="linkAddress">The link address to be set</param>
public void SetHyperlink(int rowNum, String columnHeader, String linkAddress)
{
CheckPreRequisites();
HSSFWorkbook workbook = OpenFileForReading();
HSSFSheet worksheet = GetWorkSheet(workbook);
HSSFRow row = (HSSFRow)worksheet.GetRow(0); //0 because header is always in the first row
int columnNum = -1;
String currentValue;
for (int currentColumnNum = 0;
currentColumnNum < row.LastCellNum; currentColumnNum++)
{
currentValue = GetCellValue(worksheet, row, currentColumnNum);
if (currentValue.Equals(columnHeader))
{
columnNum = currentColumnNum;
break;
}
}
if (columnNum == -1)
{
throw new FrameworkException("The specified column header " + columnHeader + " is not found in the sheet \"" + DatasheetName + "\"!");
}
else
{
row = (HSSFRow)worksheet.GetRow(rowNum);
HSSFCell cell = (HSSFCell)row.GetCell(columnNum);
if (cell == null)
{
throw new FrameworkException("Specified cell is empty! Please set a value before including a hyperlink...");
}
SetCellHyperlink(workbook, cell, linkAddress);
WriteIntoFile(workbook);
}
}
