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();
}
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);
}
/// <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);
}
}
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>
/// 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 int Replace(string a_OldValue, string a_NewValue, string a_SheetName)
{
int occurences = 0;
HSSFSheet a_sheet = null;
Open();
// Parse all sheets
for (int SheetIndex = 0; SheetIndex < m_CurrWorkbook.NumberOfSheets; SheetIndex++)
{
//Get the sheet
a_sheet = (HSSFSheet)m_CurrWorkbook.GetSheetAt(SheetIndex);
if (a_sheet.SheetName.Equals(a_SheetName))
{
//get the rows
IEnumerator a_enum = a_sheet.GetRowEnumerator();
//Parse the rows for the value
int aint = 0;
while (a_enum.MoveNext())
{
Console.WriteLine(aint);
aint += 1;
HSSFRow arow = (HSSFRow)a_enum.Current;
//The LastCellNum property indicates the far right cell
//available in the sheet.DO NOT USE Cells.Count.
for (int i = 0; i < arow.LastCellNum; i++)
{
if (arow.GetCell(i) != null)
{
if (arow.GetCell(i).ToString().Equals(a_OldValue))
{
arow.GetCell(i).SetCellValue(a_NewValue);
occurences += 1;
}
}
} //for
} //while
} //if
} //for
return(occurences);
}
public static void DataTable2Excel(System.Data.DataTable dtData, string filename)
{
//DataSet DS = new DataSet();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("工作表名稱");
//顯示 Table 0 的所有欄位名稱
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dtData.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//顯示 所有資料列
int rowIndex = 1;
foreach (DataRow row in dtData.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtData.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
dataRow.GetCell(column.Ordinal).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
}
dataRow = null;
rowIndex++;
}
//Response.Clear();
// 產生 Excel 資料流
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
headerRow = null;
sheet = null;
workbook = null;
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// 設定強制下載標頭
curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + ".xls"));
// 輸出檔案
curContext.Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
curContext.Response.End();
}
public DataTable ImportExcelFile(string filePath)
{
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());//添加列名
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return(dt);
}
/// <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 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);
}
/// <summary>
/// 读取Excle文件数据
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="HeaderRowIndex">表头所在的行索引</param>
/// <returns></returns>
public static DataSet RenderDataFromExcel(string filePath, int HeaderRowIndex = 0)
{
if (string.IsNullOrWhiteSpace(filePath) || !(filePath.EndsWith(".xls") || filePath.EndsWith(".xlsx")) || !File.Exists(filePath))
{
return(null);
}
DataSet ds = new DataSet();
Stream ExcelFileStream = File.OpenRead(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
HSSFSheet sheet = null;
DataTable table = null;
int sheetCount = workbook.NumberOfSheets;
for (int s = 0; s < sheetCount; s++)
{
sheet = (HSSFSheet)workbook.GetSheetAt(s);
table = new DataTable(sheet.SheetName);
HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet.LastRowNum;
for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
ds.Tables.Add(table);
}
ExcelFileStream.Close();
workbook = null;
sheet = null;
table = null;
return(ds);
}
private DataTable GetSourceFromExcel(string file)
{
DataTable dtPatterns = new DataTable();
int column = 0;
int row = -1;
HSSFWorkbook hssfworkbook;
FileStream fileName = new FileStream(file, FileMode.Open, FileAccess.Read);
hssfworkbook = new HSSFWorkbook(fileName);
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
if (sheet1.LastRowNum > 0)
{
for (row = -1; row <= sheet1.LastRowNum; row++)
{
HSSFRow dataRow = (HSSFRow)sheet1.GetRow(row);
if (row.Equals(-1))
{
dtPatterns.Columns.Add("MatricNo", typeof(string));
//dtPatterns.Columns.Add("StudentName", typeof(string));
//dtPatterns.Columns.Add("Program", typeof(string));
//dtPatterns.Columns.Add("Semester", typeof(string));
dtPatterns.Columns.Add("ICNo", typeof(string));
dtPatterns.Columns.Add("SponsorAmount", typeof(double));
//dtPatterns.Columns.Add("PocketAmount", typeof(double));
}
else
{
DataRow dr = dtPatterns.NewRow();
//Add the value to the row
for (column = 0; column < dataRow.Cells.Count; column++)
{
dr[column] = dataRow.Cells[column].ToString();
}
dtPatterns.Rows.Add(dr);
}
}
}
return(dtPatterns);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/x-excel";
string filename = HttpUtility.UrlEncode("企业信息数据.xls");
context.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet();
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
row.CreateCell(0, NPOI.SS.UserModel.CellType.String).SetCellValue("企业名称");
row.CreateCell(1, NPOI.SS.UserModel.CellType.String).SetCellValue("企业电话");
row.CreateCell(2, NPOI.SS.UserModel.CellType.String).SetCellValue("企业邮箱");
row.CreateCell(3, NPOI.SS.UserModel.CellType.String).SetCellValue("企业网址");
using (SqlConnection conn = new SqlConnection("Data Source=SERVER;Initial Catalog=database;User ID=sa;Password="******"select CompanyName,CompanyPhone,CompanyEmail,CompanyUrl from CompanyInfo";
using (IDataReader reader = cmd.ExecuteReader())
{
int rownum = 1;
while (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("CompanyName"));
string phone = reader.GetString(reader.GetOrdinal("CompanyPhone"));
string email = reader.GetString(reader.GetOrdinal("CompanyEmail"));
string urls = reader.GetString(reader.GetOrdinal("CompanyUrl"));
row = (HSSFRow)sheet.CreateRow(rownum);
row.CreateCell(0, NPOI.SS.UserModel.CellType.String).SetCellValue(name);
row.CreateCell(1, NPOI.SS.UserModel.CellType.String).SetCellValue(phone);
row.CreateCell(2, NPOI.SS.UserModel.CellType.String).SetCellValue(email);
row.CreateCell(3, NPOI.SS.UserModel.CellType.String).SetCellValue(urls);
rownum++;
}
}
}
}
hssfworkbook.Write(context.Response.OutputStream);
}
/// <summary>
/// 将用户输入的直径、高、质量读入到变量, 将Sheet中的载荷、位移数据 读入到list中
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="isCalDensity"></param>
private void SheetRead(int sheetIndex, DataRow dataRow, Boolean isCalDensity = false)
{
currentSheetIndex = sheetIndex;
// 将用户输入的直径、高、质量读入到变量
current_line_Diameter = dataRow.GetDiameter();
current_line_Height = dataRow.GetHeight();
if (isCalDensity)
{
current_line_Mass = dataRow.GetMass();
}
// 将Sheet中的载荷、位移数据 读入到list中
using (var file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
var rows = Sheets[sheetIndex].GetEnumerator();
rows.MoveNext(); // 跳过第一行文字
while (rows.MoveNext())
{
try
{
HSSFRow row = (HSSFRow)rows.Current;
current_sheet_force_data.Add(row.Cells[1].NumericCellValue);
current_sheet_displacement_data.Add(row.Cells[2].NumericCellValue);
Console.WriteLine("载荷：" + row.Cells[1] + "kN, 位移: " + row.Cells[2] + "mm.");
Console.WriteLine("载荷type：" + row.Cells[1].NumericCellValue.ToString() + "kN, 位移type: " + row.Cells[2].GetType() + "mm.");
}
catch (System.InvalidOperationException)
{
System.Windows.MessageBox.Show("请检查选择的Excel表格是否正确。");
break;
}
}
}
currentSheet = Sheets[currentSheetIndex];
Console.WriteLine(current_sheet_displacement_data.Count.ToString() + current_sheet_force_data.Count);
Console.WriteLine(current_sheet_displacement_data.Max());
}
//JAVA TO C# CONVERTER WARNING: Method 'throws' clauses are not available in C#:
//ORIGINAL LINE: private java.util.Map<String, nomitech.common.base.GroupCode> loadCSI(org.hibernate.Session paramSession, String paramString) throws Exception
private IDictionary <string, GroupCode> loadCSI(Session paramSession, string paramString)
{
for (sbyte b = 1; b <= 3; b++)
{
POIFSFileSystem pOIFSFileSystem = new POIFSFileSystem(new FileStream(paramString + b + ".xls", FileMode.Open, FileAccess.Read));
HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(pOIFSFileSystem);
this.o_evaluator = hSSFWorkbook.CreationHelper.createFormulaEvaluator();
HSSFSheet hSSFSheet = hSSFWorkbook.getSheetAt(0);
int i = getRealNumberOfRows(hSSFSheet);
for (int j = 2; j < i; j++)
{
if (j % this.rowsToCommit == 0)
{
paramSession.Transaction.commit();
paramSession.Transaction.begin();
Console.WriteLine("Processing next 500...");
}
HSSFRow hSSFRow = hSSFSheet.getRow(j);
string str = notNull(hSSFRow.getCell(0));
if (!this.csiMap.ContainsKey(str))
{
string str1 = str + "00";
string str2 = notNull(hSSFRow.getCell(1));
str1 = StringUtils.replaceAll(str1, ".", "");
int k = str2.LastIndexOf(" - ", StringComparison.Ordinal);
if (k != -1)
{
str2 = str2.Substring(k + 3);
}
GroupCode groupCode = addGroupCode2(str1, str2);
this.csiMap[str] = groupCode;
paramSession.save(groupCode);
Console.WriteLine(str1 + " = " + str2);
}
}
if (hSSFWorkbook != null)
{
hSSFWorkbook.close();
}
}
return(this.csiMap);
}
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();
}
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);
}
}
private static void createColumn(DataTable dt, NPOI.SS.UserModel.ICell cell, HSSFRow row, DataRow dr1, int start, int end)
{
DataRow dr = dt.NewRow();
int j = 0;
for (int i = start; i <= end; i++)
{
cell = row.GetCell(i);
if (cell == null)
{
dr[j] = 0;
}
else
{
dr[j] = stringToInt(cell.ToString());
}
j++;
}
dt.Rows.Add(dr);
}
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;
}
}
/// <summary>
/// 利用office组件导出Excel表格文件
/// </summary>
//public static void ExportExcelFile(DataTable ExcelTable)
//{
// try
// {
// string SaveExcelName = string.Empty;//保存的Excel文件名称
// SaveFileDialog SFDialog = new SaveFileDialog();
// SFDialog.DefaultExt = "xls";
// SFDialog.Filter = "Excel文件(*.xls)|*.xls";
// SFDialog.ShowDialog();
// SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称
// if (SaveExcelName.IndexOf(":") < 0) return;
// Microsoft.Office.Interop.Excel.Application XlsApp = new Microsoft.Office.Interop.Excel.Application();//创建Excel应用程序
// object missing = System.Reflection.Missing.Value;
// if (XlsApp == null)
// {
// MessageBoxEx.Show("无法创建Excel表格文件，您的电脑可能未安装Excel软件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return;
// }
// else
// {
// Microsoft.Office.Interop.Excel.Workbooks WkBks = XlsApp.Workbooks;//获取工作簿对像
// Microsoft.Office.Interop.Excel.Workbook WkBk = WkBks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//添加Excel表格模板
// Microsoft.Office.Interop.Excel.Worksheet WkSheet = (Microsoft.Office.Interop.Excel.Worksheet)WkBk.Worksheets[1];//获取工作表格1;
// Microsoft.Office.Interop.Excel.Range Ran;//声明Excel表格
// int TotalCount = ExcelTable.Rows.Count;
// //int rowRead = 0;//读取行数
// //float PercentRead = 0;//导入百分比
// //写入字段名
// for (int i = 0; i < ExcelTable.Columns.Count; i++)
// {
// WkSheet.Cells[1, i + 1] = ExcelTable.Columns[i].ColumnName.ToString();//获取表列名称
// Ran = (Microsoft.Office.Interop.Excel.Range)WkSheet.Cells[1, i + 1];//列名称写入单元格
// Ran.Interior.ColorIndex = 15;
// Ran.Font.Bold = true;//标题加粗
// }
// //ProgressBarMsg ProgBarMsg = new ProgressBarMsg();
// //ProgBarMsg.MaxNum = TotalCount;//获取总记录行项
// //ProgBarMsg.ShowDialog();//显示进度条
// //写字段值
// for (int j = 0; j < ExcelTable.Rows.Count; j++)
// {
// for (int k = 0; k < ExcelTable.Columns.Count; k++)
// {
// WkSheet.Cells[j + 2, k + 1] = ExcelTable.Rows[j][k].ToString();//写表格值
// }
// //rowRead++;
// //PercentRead = ((float)rowRead * 100) / TotalCount;//导入进度百分比
// //ProgressBarMsg.PercentMsg = rowRead;
// //Thread.Sleep(200);
// Application.DoEvents();//处理当前在消息队列中所有windows消息
// }
// WkSheet.SaveAs(SaveExcelName, missing, missing, missing, missing, missing, missing, missing, missing);
// Ran = WkSheet.get_Range((object)WkSheet.Cells[2, 1], (object)WkSheet.Cells[ExcelTable.Rows.Count + 1, ExcelTable.Columns.Count]);//给工作表指定区域
// //设置Excel表格边框样式
// Ran.BorderAround2(missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
// Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, missing, missing);
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;//设置区域边框颜色
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//连续边框
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框浓度
// if (ExcelTable.Columns.Count > 1)
// {//设置垂直表格颜色索引
// Ran.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
// }
// WkBk.Save();//保存Excel表数据
// //关闭表格对像，并退出应用程序域
// WkBk.Close(missing, missing, missing);
// XlsApp.Quit();
// XlsApp = null;
// GC.Collect();//强制关闭
// MessageBoxEx.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// }
// }
// catch (Exception ex)
// {
// MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
// }
//}
/// <summary>
/// 设置导出Excel标题样式
/// </summary>
/// <param name="HeaderRow">标题行</param>
/// <param name="WkBk">工作表</param>
/// <param name="ColNum">列序号</param>
private static void SetXlsHeaderStyle(HSSFRow HeaderRow, HSSFWorkbook WkBk, int ColNum)
{
try
{
HSSFCellStyle HeaderStyle = (HSSFCellStyle)WkBk.CreateCellStyle(); //创建Excel表格标题样式
HSSFFont fontStyle = (HSSFFont)WkBk.CreateFont(); //创建Excel表格字体样式
HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //设置居中
HeaderStyle.FillForegroundColor = HSSFColor.Lime.Index; //设置背景色
HeaderStyle.FillPattern = FillPattern.SolidForeground; //设置填充样式
HeaderStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); //设置数据格式
fontStyle.FontHeightInPoints = 11;
fontStyle.Boldweight = 700;
HeaderStyle.SetFont(fontStyle);
HeaderRow.GetCell(ColNum).CellStyle = (ICellStyle)HeaderStyle;//设置列样式
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
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 void TestGroupRows()
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = workbook.CreateSheet();
HSSFRow r1 = (HSSFRow)s.CreateRow(0);
HSSFRow r2 = (HSSFRow)s.CreateRow(1);
HSSFRow r3 = (HSSFRow)s.CreateRow(2);
HSSFRow r4 = (HSSFRow)s.CreateRow(3);
HSSFRow r5 = (HSSFRow)s.CreateRow(4);
Assert.AreEqual(0, r1.OutlineLevel);
Assert.AreEqual(0, r2.OutlineLevel);
Assert.AreEqual(0, r3.OutlineLevel);
Assert.AreEqual(0, r4.OutlineLevel);
Assert.AreEqual(0, r5.OutlineLevel);
s.GroupRow(2, 3);
Assert.AreEqual(0, r1.OutlineLevel);
Assert.AreEqual(0, r2.OutlineLevel);
Assert.AreEqual(1, r3.OutlineLevel);
Assert.AreEqual(1, r4.OutlineLevel);
Assert.AreEqual(0, r5.OutlineLevel);
// Save and re-Open
workbook = HSSFTestDataSamples.WriteOutAndReadBack(workbook);
s = workbook.GetSheetAt(0);
r1 = (HSSFRow)s.GetRow(0);
r2 = (HSSFRow)s.GetRow(1);
r3 = (HSSFRow)s.GetRow(2);
r4 = (HSSFRow)s.GetRow(3);
r5 = (HSSFRow)s.GetRow(4);
Assert.AreEqual(0, r1.OutlineLevel);
Assert.AreEqual(0, r2.OutlineLevel);
Assert.AreEqual(1, r3.OutlineLevel);
Assert.AreEqual(1, r4.OutlineLevel);
Assert.AreEqual(0, r5.OutlineLevel);
}
/// <summary>
/// 將DataTable轉成Stream輸出.
/// </summary>
/// <param name="SourceTable">The source table.</param>
/// <returns></returns>
public static Stream RenderDataTableToExcel(DataTable SourceTable, string sheetName = "sheet1")
{
var workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
HSSFSheet sheet = workbook.CreateSheet(sheetName) as HSSFSheet;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
// handling header.
foreach (DataColumn column in SourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in SourceTable.Rows)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in SourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
for (var i = 0; i < SourceTable.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
/// <summary>
/// 從位元流讀取資料到DataTable.
/// </summary>
/// <param name="ExcelFileStream">The excel file stream.</param>
/// <param name="SheetIndex">Index of the sheet.</param>
/// <param name="HeaderRowIndex">Index of the header row.</param>
/// <param name="HaveHeader">if set to <c>true</c> [have header].</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex, bool HaveHeader)
{
HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
HSSFSheet sheet = workbook.GetSheetAt(SheetIndex) as HSSFSheet;
DataTable table = new DataTable();
HSSFRow headerRow = sheet.GetRow(HeaderRowIndex) as HSSFRow;
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
string ColumnName = (HaveHeader == true) ? headerRow.GetCell(i).StringCellValue : "f" + i.ToString();
DataColumn column = new DataColumn(ColumnName);
table.Columns.Add(column);
}
int rowCount = sheet.LastRowNum;
int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + 1 : sheet.FirstRowNum;
for (int i = RowStart; i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
table.Rows.Add(dataRow);
}
ExcelFileStream.Close();
workbook = null;
sheet = null;
return(table);
}
/// <summary>
/// Excel表中有多张Sheet表，操作返回DataSet
/// </summary>
/// <param name="stream"></param>
/// <returns></returns>
public DataSet ReadExcelToDataSet(Stream stream)
{
DataSet ds = new DataSet();
DataTable dt;
workBook = new HSSFWorkbook(stream);
int sheetsCount = workBook.NumberOfSheets;
for (int k = 0; k < sheetsCount; k++)
{
dt = new DataTable();
workSheet = workBook.GetSheetAt(k);
var rows = workSheet.GetRowEnumerator();
rows.MoveNext();
HSSFRow row = (HSSFRow)rows.Current;
for (int i = 0; i < workSheet.GetRow(0).LastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue);
}
while (rows.MoveNext())
{
row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell != null)
{
dr[i] = cell.ToString();
}
else
{
dr[i] = null;
}
}
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
}
return(ds);
}
protected void SetCell(HSSFRow row
, DataRow record
, DataRow recordBefore
, int index
, string columnName
, bool isDuplicateHide = false
, ICustomFormula formula = null)
{
HSSFCell _cell;
object _recordValue;
object _recordBeforeValue;
_cell = row.CreateCell(index) as HSSFCell;
_recordValue = record[columnName];
_recordBeforeValue = recordBefore[columnName];
if (Convert.IsDBNull(_recordValue) == true)
{
return;
}
if (formula != null)
{
_recordValue = formula.GetValue(_recordValue);
}
_cell.SetCellValue(_recordValue.ToString());
//確認是否有隱藏重複資料
if (isDuplicateHide == true)
{
if (Convert.IsDBNull(_recordValue) == false)
{
if (_recordValue.Equals(_recordBeforeValue) == true)
{
_cell.SetCellValue(string.Empty);
}
}
}
}
/// <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);
}
}
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>
/// 用NPOI直接读取excel返回DataSet
/// </summary>
/// <param name="excelFileStream">FileStream fs = File.Open(dlg.FileName, FileMode.Open);</param>
/// <param name="sheetCount">sheet数量</param>
/// <returns></returns>
public static DataSet ReadExcelToDataSet(Stream excelFileStream, int sheetCount)
{
HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
DataSet ds = new DataSet();
for (int k = 0; k < sheetCount; k++)
{
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(k);
DataTable table = new DataTable("table" + k);
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
string columnName = headerRow.GetCell(i).StringCellValue;
DataColumn column = new DataColumn(columnName);
if (!table.Columns.Contains(columnName))
{
table.Columns.Add(column);
}
}
int rowCount = sheet.LastRowNum;
for (int i = (0 + 1); i <= rowCount; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j);
}
}
table.Rows.Add(dataRow);
}
ds.Tables.Add(table);
}
excelFileStream.Close();
return(ds);
}
public static DataTable ImportExcelFile(string filePath)
{
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssworkbook = new HSSFWorkbook(file);
}
}
catch (Exception)
{
}
NPOI.SS.UserModel.ISheet sheet = hssworkbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
dt.Columns.Add(Convert.ToChar((int)'A' + i) + "");
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int j = 0; j < row.LastCellNum; j++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(j);
if (cell == null)
{
dr[j] = null;
}
else
{
dr[j] = cell + "";
}
}
dt.Rows.Add(dr);
}
return(dt);
}
