public List<StockDetail> ImportToXls(string fileName, ref string ErrMsg)
{
List<StockDetail> list = new List<StockDetail>();
try
{
using (var file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite))
{
string strExt = System.IO.Path.GetExtension(fileName);
IWorkbook wb;// = new XSSFWorkbook(file);
if (strExt.Equals(".xls"))
{
wb = new HSSFWorkbook(file);
}
else
wb = new XSSFWorkbook(file);
ISheet sheet = wb.GetSheetAt(0);
for (int i = 0; i <= sheet.LastRowNum; i++)
{
try
{
IRow row = sheet.GetRow(i);
for (int j = 0; j < 1; j++)
{
ICell readCell = row.GetCell(j);
StockDetail glass = new StockDetail();
glass.GlassID = readCell.ToString().ToUpper();
readCell.ToString();//这就是当前格子的值了
glass.Qty = 1;
glass.AccountID = CurrentAccount.ID;
glass.AccountName = CurrentAccount.Name;
glass.Status = 0;
glass.CreateDt = DateTime.Now;
if (list.Any(p => p.GlassID.Equals(glass.GlassID)))
{
ErrMsg = "Excel中存在重复的GlassID";
return null;
}
list.Add(glass);
}
}
catch { }
}
}
}
catch (Exception ex)
{
ErrMsg = ex.Message;
}
if (list.Count() <= 0)
{
ErrMsg = "当前不存在需要导入的GlassID，请检查Excel格式";
}
return list;
}
public IWorkbook GetIWorkbook(string filePath)
{
IWorkbook iworkbook = null;
try
{
if (filePath.EndsWith(".xls"))
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
iworkbook = new HSSFWorkbook(file);
}
}
else if (filePath.EndsWith(".xlsx"))
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
iworkbook = new XSSFWorkbook(file);
}
}
}
catch (Exception et)
{
throw et;
}
return iworkbook;
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
// Create a Worksheet
ISheet ws = wb.CreateSheet("Sheet1");
// Aqua background
ICellStyle style = wb.CreateCellStyle();
style.FillBackgroundColor = IndexedColors.Aqua.Index;
style.FillPattern = FillPattern.BigSpots;
IRow row = ws.CreateRow(0);
ICell cell = row.CreateCell(1);
cell.SetCellValue("X");
cell.CellStyle = style;
// Orange "foreground", foreground being the fill foreground not the font color.
style = wb.CreateCellStyle();
style.FillBackgroundColor = IndexedColors.Orange.Index;
style.FillPattern = FillPattern.SolidForeground;
cell = row.CreateCell(2);
cell.SetCellValue("X");
cell.CellStyle = style;
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet s1 = workbook.CreateSheet("Sheet1");
ICellStyle rowstyle = workbook.CreateCellStyle();
rowstyle.FillForegroundColor = IndexedColors.Red.Index;
rowstyle.FillPattern = FillPattern.SolidForeground;
ICellStyle c1Style = workbook.CreateCellStyle();
c1Style.FillForegroundColor = IndexedColors.Yellow.Index;
c1Style.FillPattern = FillPattern.SolidForeground;
IRow r1 = s1.CreateRow(1);
IRow r2= s1.CreateRow(2);
r1.RowStyle = rowstyle;
r2.RowStyle = rowstyle;
ICell c1 = r2.CreateCell(2);
c1.CellStyle = c1Style;
c1.SetCellValue("Test");
ICell c4 = r2.CreateCell(4);
c4.CellStyle = c1Style;
using(var fs=File.Create("test.xlsx"))
{
workbook.Write(fs);
}
}
public void Create(Bilan bilan, string path)
{
var splitPath = path.Split('\\');
var pathFile = splitPath.Take(splitPath.Count() - 1).Aggregate((e, p) => e + "\\" + p) + "\\";
var fileNameOld = splitPath.Last();
var fileName = "synoptique_" + fileNameOld;
using (var stream = new FileStream(pathFile + fileName, FileMode.Create, FileAccess.Write))
{
var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("synoptique");
var cadreArmoire = new CadreArmoire(sheet, workbook);
cadreArmoire.Create(bilan.PageDeGarde.ChambrePMZ);
var cadrePA = new CadrePA(sheet, workbook);
cadrePA.Create(bilan.PageDeGarde.ChambrePointAboutement);
var positionnementEtudeCreator = new PositionnementEtudeCreator(sheet, workbook);
var hauteur = 9;
var numeroCassette = 0;
foreach (var positionnementEtude in bilan.PositionnementEtudes)
{
positionnementEtudeCreator.Create(positionnementEtude, ref hauteur, ref numeroCassette);
}
SetWidth(sheet);
workbook.Write(stream);
}
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PictureSheet");
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
//first picture
int imageId= LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
//second picture
int imageId2 = LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(500, 200, 0, 0, 5, 10, 7, 15);
XSSFPicture picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, imageId2);
picture.LineStyle = LineStyle.DashDotGel;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("linechart");
// Create a row and put some cells in it. Rows are 0 based.
IRow row;
ICell cell;
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++)
{
row = sheet.CreateRow((short)rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++)
{
cell = row.CreateCell((short)colIndex);
cell.SetCellValue(colIndex * (rowIndex + 1));
}
}
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor1 = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15);
CreateChart(drawing, sheet, anchor1, "title1","title2");
IClientAnchor anchor2 = drawing.CreateAnchor(0, 0, 0, 0, 0, 20, 10, 35);
CreateChart(drawing, sheet, anchor2, "s1", "s2");
using (FileStream fs =File.Create("test.xlsx"))
{
wb.Write(fs);
}
}
public static IWorkbook WriteOutAndReadBack(IWorkbook wb)
{
IWorkbook result;
try
{
using (MemoryStream baos = new MemoryStream(8192))
{
wb.Write(baos);
using (Stream is1 = new MemoryStream(baos.ToArray()))
{
if (wb is HSSFWorkbook)
{
result = new HSSFWorkbook(is1);
}
else if (wb is XSSFWorkbook)
{
result = new XSSFWorkbook(is1);
}
else
{
throw new RuntimeException("Unexpected workbook type ("
+ wb.GetType().Name + ")");
}
}
}
}
catch (IOException e)
{
throw new RuntimeException(e);
}
return result;
}
public void TestXSSFSetArrayFormula_multiCell()
{
ICellRange<ICell> cells;
String formula2 = "456";
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CellRangeAddress range = CellRangeAddress.ValueOf("C4:C6");
cells = sheet.SetArrayFormula(formula2, range);
Assert.AreEqual(3, cells.Size);
// sheet.SetArrayFormula Creates rows and cells for the designated range
/*
* From the spec:
* For a multi-cell formula, the c elements for all cells except the top-left
* cell in that range shall not have an f element;
*/
// Check that each cell exists and that the formula text is Set correctly on the first cell
XSSFCell firstCell = (XSSFCell)cells.TopLeftCell;
ConfirmArrayFormulaCell(firstCell, "C4", formula2, "C4:C6");
ConfirmArrayFormulaCell(cells.GetCell(1, 0), "C5");
ConfirmArrayFormulaCell(cells.GetCell(2, 0), "C6");
Assert.AreSame(firstCell, sheet.GetFirstCellInArrayFormula(firstCell));
}
public void TestCustomProperties()
{
POIXMLDocument wb = new XSSFWorkbook();
CustomProperties customProps = wb.GetProperties().CustomProperties;
customProps.AddProperty("test-1", "string val");
customProps.AddProperty("test-2", 1974);
customProps.AddProperty("test-3", 36.6);
//Adding a duplicate
try
{
customProps.AddProperty("test-3", 36.6);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("A property with this name already exists in the custom properties", e.Message);
}
customProps.AddProperty("test-4", true);
wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack((XSSFWorkbook)wb);
CT_CustomProperties ctProps =
wb.GetProperties().CustomProperties.GetUnderlyingProperties();
Assert.AreEqual(6, ctProps.sizeOfPropertyArray());
CT_Property p;
p = ctProps.GetPropertyArray(0);
Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid);
Assert.AreEqual("test-1", p.name);
Assert.AreEqual("string val", p.Item.ToString());
Assert.AreEqual(2, p.pid);
p = ctProps.GetPropertyArray(1);
Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid);
Assert.AreEqual("test-2", p.name);
Assert.AreEqual(1974, p.Item);
Assert.AreEqual(3, p.pid);
p = ctProps.GetPropertyArray(2);
Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid);
Assert.AreEqual("test-3", p.name);
Assert.AreEqual(36.6, p.Item);
Assert.AreEqual(4, p.pid);
p = ctProps.GetPropertyArray(3);
Assert.AreEqual("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", p.fmtid);
Assert.AreEqual("test-4", p.name);
Assert.AreEqual(true, p.Item);
Assert.AreEqual(5, p.pid);
p = ctProps.GetPropertyArray(4);
Assert.AreEqual("Generator", p.name);
Assert.AreEqual("NPOI", p.Item);
Assert.AreEqual(6, p.pid);
//p = ctProps.GetPropertyArray(5);
//Assert.AreEqual("Generator Version", p.name);
//Assert.AreEqual("2.0.9", p.Item);
//Assert.AreEqual(7, p.pid);
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
int x = 1;
for (int i = 0; i < 15; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < 15; j++)
{
ICell cell = row.CreateCell(j);
if (x % 2 == 0)
{
//fill background with blue
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index2;
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
cell.CellStyle = style1;
}
else
{
//fill background with yellow
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2;
style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
cell.CellStyle = style1;
}
x++;
}
}
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// use me for 2003 and 2007
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public DataTable ReadExcelAsTable(string fileName)
{
try
{
using (FileStream fs = new FileStream(fileName, FileMode.Open))
{
//chk xls
SS.UserModel.ISheet sheet;
if (fileName.EndsWith("xlsx"))
{
XSSF.UserModel.XSSFWorkbook wb = new XSSF.UserModel.XSSFWorkbook(fs); //for 2007
sheet = wb.GetSheetAt(0); //read first sheet
}
else
{
SS.UserModel.IWorkbook wb = new HSSF.UserModel.HSSFWorkbook(fs); //for 2003
sheet = wb.GetSheetAt(0); //read first sheet
}
return(ReadSheet(sheet));
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet s1=wb.CreateSheet("Monthly Salary Report");
IRow headerRow = s1.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("First Name");
s1.SetColumnWidth(0, 20 * 256);
headerRow.CreateCell(1).SetCellValue("Last Name");
s1.SetColumnWidth(1, 20 * 256);
headerRow.CreateCell(2).SetCellValue("Salary");
headerRow.CreateCell(3).SetCellValue("Tax Rate");
headerRow.CreateCell(4).SetCellValue("Tax");
headerRow.CreateCell(5).SetCellValue("Delivery");
int row = 1;
GenerateRow(s1, row++, "Bill", "Zhang", 5000, 9.0/100);
GenerateRow(s1, row++, "Amy", "Huang", 8000, 11.0/100);
GenerateRow(s1, row++, "Tomos", "Johnson", 6000, 9.0/100);
GenerateRow(s1, row++, "Macro", "Jeep", 12000, 15.0/100);
s1.ForceFormulaRecalculation = false;
FileStream fs = File.Create("test.xlsx");
wb.Write(fs);
fs.Close();
}
public CadrePA(ISheet sheet, XSSFWorkbook workbook)
{
_sheet = sheet;
_workbook = workbook;
_lineCreator = new DataLineStyle(workbook, sheet);
_cadreCreator = new Cadre(workbook, sheet);
}
public void TestValidXSSF()
{
XSSFWorkbook wb = new XSSFWorkbook();
Evaluator = new XSSFFormulaEvaluator(wb);
Confirm(wb);
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("First Sheet");
ISheet sheet2 = wb.CreateSheet("Second Sheet");
// Note that sheet name is Excel must not exceed 31 characters
// and must not contain any of the any of the following characters:
// 0x0000
// 0x0003
// colon (:)
// backslash (\)
// asterisk (*)
// question mark (?)
// forward slash (/)
// opening square bracket ([)
// closing square bracket (])
// You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
// for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
String safeName = WorkbookUtil.CreateSafeSheetName("[O'Brien's sales*?]");
ISheet sheet3 = wb.CreateSheet(safeName);
FileStream sw = File.Create("newWorksheet.xls");
wb.Write(sw);
sw.Close();
}
public ExcelParser(string fileName)
{
FileStream fs = new FileStream(fileName,
FileMode.Open);
template = new XSSFWorkbook(fs);
TestCases = new List<TestCase>();
}
public void TestExcel()
{
POIXMLDocument doc = new XSSFWorkbook(
POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("ExcelWithAttachments.xlsm")
);
Test(doc, 4);
}
public static int[][][] ReadExcelPattern(string filepath)
{
using (FileStream fs = new FileStream(filepath,FileMode.Open,FileAccess.Read))
{
IWorkbook workbook = new XSSFWorkbook(fs);
int sheetnumber = workbook.NumberOfSheets;
int [][][] sheetelements = new int[sheetnumber][][];
for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){
ISheet _isheet = workbook.GetSheetAt(sheetindex);
int lastrownum = _isheet.LastRowNum;
int [][] rowelements = new int[lastrownum+1][];
for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){
IRow row = _isheet.GetRow(rowindex);
if(row == null)continue;
int lastcellnum = row.LastCellNum;
int[] cellelements = new int[lastcellnum+1];
for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){
ICell cell = row.GetCell(cellindex);
if(cell != null){
cellelements[cellindex] = Convert.ToInt32(cell.ToString());
Debug.Log(cellelements[cellindex]);
}
}
rowelements[rowindex] = cellelements;
}
sheetelements[sheetindex] = rowelements;
}
return sheetelements;
}
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
// Create a Worksheet
ISheet ws = wb.CreateSheet("Sheet1");
ICellStyle style = wb.CreateCellStyle();
//Setting the line of the top border
style.BorderTop = BorderStyle.Thick;
style.TopBorderColor = 256;
style.BorderLeft = BorderStyle.Thick;
style.LeftBorderColor = 256;
style.BorderRight = BorderStyle.Thick;
style.RightBorderColor = 256;
style.BorderBottom = BorderStyle.Thick;
style.BottomBorderColor = 256;
IRow row = ws.CreateRow(0);
ICell cell = row.CreateCell(1);
cell.CellStyle = style;
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();
}
public Dictionary<DateTime, List<SaleParsed>> ImportFromSheet(string path, DateTime dateTime, IEnumerable<int> sheetNumbers, IEnumerable<GoodType> goodTypes, string producer)
{
this.goodTypes = goodTypes;
this.producer = producer;
var result = new Dictionary<DateTime, List<SaleParsed>>();
using (var fs = File.OpenRead(path))
{
var workbook = new XSSFWorkbook(fs);
foreach (var sNum in sheetNumbers)
{
ISheet sheet = workbook.GetSheetAt(sNum);
var sales = ParseSales(sheet, dateTime);
foreach (var s in sales)
{
if (result.ContainsKey(s.Key))
{
result[s.Key].AddRange(s.Value);
}
else
{
result.Add(s.Key, s.Value);
}
}
}
}
return result;
}
public void TestNoColsWithoutWidthWhenGroupingAndCollapsing()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("test");
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 5000);
sheet.GroupColumn((short)4, (short)5);
sheet.SetColumnGroupCollapsed(4, true);
CT_Cols cols = sheet.GetCTWorksheet().GetColsArray(0);
//logger.log(POILogger.DEBUG, "test52186_2/cols:" + cols);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb, "testNoColsWithoutWidthWhenGroupingAndCollapsing");
sheet = (XSSFSheet)wb.GetSheet("test");
for (int i = 4; i <= 5; i++)
{
Assert.AreEqual(5000, sheet.GetColumnWidth(i), "Unexpected width of column " + i);
}
cols = sheet.GetCTWorksheet().GetColsArray(0);
foreach (CT_Col col in cols.GetColArray())
{
Assert.IsTrue(col.IsSetWidth(), "Col width attribute is unset: " + col.ToString());
}
}
public void TestBug48936()
{
IWorkbook w = new XSSFWorkbook();
ISheet s = w.CreateSheet();
int i = 0;
List<String> lst = ReadStrings("48936-strings.txt");
foreach (String str in lst)
{
s.CreateRow(i++).CreateCell(0).SetCellValue(str);
}
try
{
w = XSSFTestDataSamples.WriteOutAndReadBack(w);
}
catch (POIXMLException)
{
Assert.Fail("Detected Bug #48936");
}
s = w.GetSheetAt(0);
i = 0;
foreach (String str in lst)
{
String val = s.GetRow(i++).GetCell(0).StringCellValue;
Assert.AreEqual(str, val);
}
}
public string Read(string inFile)
{
var stream = File.OpenRead(inFile);
var book = new XSSFWorkbook(stream);
stream.Close();
var sb = new StringBuilder();
var sheet = book.GetSheetAt(0);
int lastRowNum = sheet.LastRowNum;
for (int r = 0; r <= lastRowNum; r++)
{
var datarow = sheet.GetRow(r);
{
foreach (var cell in datarow.Cells)
{
switch (cell.CellType)
{
case CellType.Numeric:
sb.Append(cell.NumericCellValue.ToString() + "\t");
break;
case CellType.String:
sb.Append(cell.StringCellValue.Replace("\n", "") + "\t");
break;
default:
throw new Exception("?");
}
}
sb.Append("\r\n");
}
}
return sb.ToString();
}
public static bool UpdateExcelData(string excelFilePath, string excelName)
{
XSSFWorkbook excelBook = new XSSFWorkbook(File.Open(excelFilePath, FileMode.Open));
var sheet = excelBook.GetSheetAt(0);
var headerRow = sheet.GetRow(0);
//total columns
int cellCount = headerRow.LastCellNum;
//total rows
int rowCount = sheet.LastRowNum;
switch (excelName)
{
case Constant.Water:
UpdateWaterData(sheet, cellCount, rowCount);
break;
case Constant.Air:
UpdateAirData(sheet, cellCount, rowCount);
break;
}
try
{
FileStream writefile = new FileStream("d:\\" + excelName + ".xlsx", FileMode.Create, FileAccess.Write);
excelBook.Write(writefile);
writefile.Close();
return true;
}
catch
{
return false;
}
}
/// <summary>
/// return File(bs, "application/vnd.ms-excel");
/// </summary>
/// <param name="tb"></param>
/// <returns></returns>
public static byte[] DataTableToExcel(DataTable tb)
{
tb = tb ?? throw new Exception($"无法把空{nameof(DataTable)}转成Excel");
using (var ms = new MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet");
var style = GetStyle(workbook,
NPOI.HSSF.Util.HSSFColor.White.Index, NPOI.HSSF.Util.HSSFColor.Black.Index);
for (int i = 0; i < tb.Rows.Count; ++i)
{
var row = sheet.CreateRow(i);
for (int j = 0; j < tb.Columns.Count; ++j)
{
var cell = row.CreateCell(j);
var data = tb.Rows[i][j];
cell.SetCellValue(ConvertHelper.GetString(data));
cell.CellStyle = style;
}
}
workbook.Write(ms);
workbook.Clear();
tb.Clear();
var bs = ms.ToArray();
return(bs);
}
}
public void TestCreate()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType);
anchor.AnchorType = (int)AnchorType.DontMoveAndResize;
Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType);
XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.IsTrue(anchor.Equals(shape.GetAnchor()));
Assert.IsNotNull(shape.PictureData);
Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data));
CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0];
// STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE
Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs);
}
public static DataSet ExcelImport(string strFileName, Tk5ListMetaData metaInfos, ResultHolder resultHolder)
{
DataSet dataSet = new DataSet();
DataTable dataTable = DataSetUtil.CreateDataTable(metaInfos.Table.TableName, metaInfos.Table.TableList);
string sheetName = metaInfos.Table.TableDesc;
HSSFWorkbook hssfworkbook = null;
XSSFWorkbook xssfworkbook = null;
ISheet sheet = null;
string fileExt = Path.GetExtension(strFileName);
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xls")
hssfworkbook = new HSSFWorkbook(file);
else if (fileExt == ".xlsx")
xssfworkbook = new XSSFWorkbook(file);
}
if (hssfworkbook != null)
{
sheet = hssfworkbook.GetSheet(sheetName);
}
else if (xssfworkbook != null)
{
sheet = xssfworkbook.GetSheet(sheetName);
}
SheetImport(metaInfos, dataTable, sheet, resultHolder);
dataSet.Tables.Add(dataTable);
return dataSet;
}
/// <summary>
/// Gets the field names from a workbook and stores them in a list.
/// </summary>
/// <param name="fieldNames">list where field names will be stored</param>
/// <param name="workbook">workbook from which field names will be extracted</param>
public static void GetFieldNames(ImportData data, XSSFWorkbook workbook)
{
ISheet sheet = workbook.GetSheetAt(0);
bool reading = true;
int rindex = sheet.FirstRowNum;
while (reading) {
IRow row = sheet.GetRow(rindex);
if (row != null) {
ICell cell = row.GetCell(0);
if (cell != null) {
string s = CellValueAsString(cell);
if (s != "" && s[0] == '[') {
for (int i = 0; i < row.LastCellNum; i++) {
s = CellValueAsString(row.GetCell(i)).TrimEnd(']').TrimStart('['); ;
data.fieldNames.Add(s);
}
// don't read more than one row of field names
reading = false;
}
}
}
rindex++;
if (rindex > sheet.LastRowNum) {
reading = false;
}
}
}
/// <summary>
/// Read an excel file and extract the meta, variables, and parsable rows.
/// </summary>
/// <param name="assetPath"></param>
public void ReadAsset(string assetPath, ref ImportData data)
{
Debug.Log("START IMPORT PROCESS FOR XLSX");
// get an absolute path to the asset
string absolutePath = System.IO.Directory.GetCurrentDirectory() + "/" + assetPath;
// open a file stream to the asset
using (FileStream fs = new FileStream(absolutePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
// get workbook
XSSFWorkbook wb = new XSSFWorkbook(fs);
// get field names
GetFieldNames(data, wb);
// get key/value meta data
GetKeyValData(ref data.meta, wb, "#");
// get key/value
GetKeyValData(ref data.vars, wb, "$");
// get the parsable rows
GetRows(data, wb);
}
}
/// <summary>
/// return File(bs, "application/vnd.ms-excel");
/// </summary>
public byte[] DataTableToExcel(DataTable tb, bool show_header = true)
{
tb = tb ?? throw new ArgumentNullException($"无法把空{nameof(DataTable)}转成Excel");
using (var ms = new MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet = workbook.CreateSheet(ValidateHelper.IsNotEmpty(tb.TableName) ? tb.TableName : "sheet");
var columns = tb.Columns.AsEnumerable_ <DataColumn>();
var row_index = 0;
IRow NewRow() => sheet.CreateRow(row_index++);
if (show_header)
{
//头部
var header_style = this.GetStyle(workbook,
NPOI.HSSF.Util.HSSFColor.Black.Index,
NPOI.HSSF.Util.HSSFColor.White.Index);
var header = NewRow();
var cell_index = 0;
foreach (var col in columns)
{
var cell = header.CreateCell(cell_index++, CellType.String);
var data = col.ColumnName;
cell.SetCellValue(data);
cell.CellStyle = header_style;
}
}
var style = this.GetStyle(workbook,
NPOI.HSSF.Util.HSSFColor.White.Index,
NPOI.HSSF.Util.HSSFColor.Black.Index);
foreach (var tb_row in tb.Rows.AsEnumerable_ <DataRow>())
{
var row = NewRow();
var cell_index = 0;
foreach (var tb_col in columns)
{
var cell = row.CreateCell(cell_index++, CellType.String);
var data = tb_row[tb_col.ColumnName];
cell.SetCellValue(ConvertHelper.GetString(data));
cell.CellStyle = style;
}
}
workbook.Write(ms);
workbook.Clear();
tb.Clear();
var bs = ms.ToArray();
return(bs);
}
}
/// <summary>
/// 写入DataTable到Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="excelFile"></param>
public virtual void writeDataTableToExcel(DataTable dt, string excelFile)
{
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region 设置Excel样式
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
#endregion
//写入基本数据
writeSheet(workbook, cellStyleA, cellStyleB, dt);
#region 输出文件
//输出到流
workbook.Write(memoryStream);
//写Excel文件
File.WriteAllBytes(excelFile, memoryStream.ToArray());
#endregion
}
public static void DataTableToExcel(string sheetName, DataTable dt, ExcelExt excelExt, Stream outStream)
{
try
{
NPOI.SS.UserModel.IWorkbook book = null;
if (excelExt == ExcelExt.Xls)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
// 添加表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.ColumnName);
index++;
}
// 添加数据
int num = dt.Rows.Count;
for (int i = 0; i < num; i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataColumn item in dt.Columns)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dt.Rows[i][item].ToString());
index++;
}
}
book.Write(outStream);
book = null;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
private static ICellStyle SetCellStyle(NPOI.XSSF.UserModel.XSSFWorkbook workbook, string color)
{
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.WrapText = true;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.FillPattern = FillPattern.SolidForeground;
switch (color)
{
case "rose":
cellStyle.FillForegroundColor = IndexedColors.Violet.Index;
break;
case "white":
cellStyle.FillForegroundColor = IndexedColors.White.Index;
break;
case "red":
cellStyle.FillForegroundColor = IndexedColors.Red.Index;
break;
case "yellow":
cellStyle.FillForegroundColor = IndexedColors.Yellow.Index;
break;
case "lime":
cellStyle.FillForegroundColor = IndexedColors.BrightGreen.Index;
break;
case "gray":
cellStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
break;
default:
cellStyle.FillForegroundColor = IndexedColors.Grey40Percent.Index;
break;
}
//CellsStyle.FillBackgroundColor = IndexedColors.BrightGreen.Index;
return(cellStyle);
}
public static byte[] Output(DataTable dataTable, string[] tableTitle)
{
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet");
IRow Title = null;
IRow rows = null;
for (int i = 1; i <= dataTable.Rows.Count; i++)
{
//创建表头
if (i - 1 == 0)
{
Title = sheet.CreateRow(0);
for (int k = 1; k < tableTitle.Length + 1; k++)
{
Title.CreateCell(0).SetCellValue("序号");
Title.CreateCell(k).SetCellValue(tableTitle[k - 1]);
}
continue;
}
else
{
rows = sheet.CreateRow(i - 1);
for (int j = 1; j <= dataTable.Columns.Count; j++)
{
rows.CreateCell(0).SetCellValue(i - 1);
rows.CreateCell(j).SetCellValue(dataTable.Rows[i - 1][j - 1].ToString());
}
}
}
byte[] buffer = new byte[1024 * 5];
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
buffer = ms.GetBuffer();
ms.Close();
}
return(buffer);
}
/// <summary>
/// return File(bs, "application/vnd.ms-excel");
/// </summary>
/// <param name="tb"></param>
/// <returns></returns>
public static byte[] DataTableToExcel(DataTable tb)
{
if (tb == null)
{
return(null);
}
using (var ms = new MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet");
var style = GetStyle(workbook,
NPOI.HSSF.Util.HSSFColor.Red.Index, NPOI.HSSF.Util.HSSFColor.White.Index);
NPOI.SS.UserModel.IRow row = null;
NPOI.SS.UserModel.ICell cell = null;
for (int i = 0; i < tb.Rows.Count; ++i)
{
row = sheet.CreateRow(i);
for (int j = 0; j < tb.Columns.Count; ++j)
{
cell = row.CreateCell(j);
cell.SetCellValue(ConvertHelper.GetString(tb.Rows[i][j]));
cell.CellStyle = style;
}
}
workbook.Write(ms);
workbook.Clear();
tb.Clear();
var bs = ms.ToArray();
return(bs);
}
}
public IHttpActionResult ExportAllToExcel(FilterSales filter)
{
var response = new DataResponse <string>();
try
{
if (filter.GroupBy <= 0)
{
#region Sales Default Export
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport");
NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper();
string[] columnname = filter.DynamicFilters.Where(a => a.IsVisible == true).Select(a => a.ColumnName).ToArray();
string[] headers, columns = null;
headers = columns = columnname;
columns = columns.Select(x => x.Replace("SalesTeam", "RepGroup")).ToArray();
columns = columns.Select(x => x.Replace("CollectedDate", "CollectionDate")).ToArray();
//byte[] rgb = new byte[3] { 22, 183, 223 };
//XSSFCellStyle HeaderCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//HeaderCellStyle.SetFillBackgroundColor(new XSSFColor(rgb));
var headerRow = sheet.CreateRow(0);
//headerRow.RowStyle.FillBackgroundColor= (short)ColorTranslator.ToWin32(Color.Aqua);
//create header
for (int i = 0; i < headers.Length; i++)
{
sheet.DefaultColumnWidth = 20;
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor colorToFill = new XSSFColor(Color.Aqua);
style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua);
headerRow.RowStyle = style;
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var dataResponse = repository.GetAllList(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath);
List <EntitySales> salesList = dataResponse.Model.List.ToList();
int recordCount = dataResponse.Model.Pager.TotalCount;
//fill content
var rowIndex = 0;
for (int i = 0; i < recordCount; i++)
{
rowIndex++;
var row = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var cell = row.CreateCell(j);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(font);
string value = null;
var objSalesItem = salesList[i];
var objStaticItem = objSalesItem.GetType().GetTypeInfo().GetProperty(columns[j]);
if (objStaticItem != null)
{
var property = salesList[i].GetType().GetRuntimeProperties().FirstOrDefault(p => string.Equals(p.Name, objStaticItem.Name, StringComparison.OrdinalIgnoreCase));
if (property != null)
{
value = Convert.ToString(property.GetValue(salesList[i], null));
}
}
else
{
var objDynamicItem = objSalesItem.ReportColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]);
if (objDynamicItem != null)
{
value = objDynamicItem.Value;
}
}
cell.SetCellValue(value);
}
bool isFinanceDataCount = salesList[i].FinanceDataRecordCount > 1;
if (isFinanceDataCount)
{
for (int k = 0; k < salesList[i].FinanceDataRecordCount; k++)
{
rowIndex++;
var financeRow = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var cell = financeRow.CreateCell(j);
string value = null;
var objSalesItem = salesList[i].FinanceDataList.ToList()[k];
var objStaticItem = objSalesItem.GetType().GetProperty(columns[j]);
if (objStaticItem != null)
{
value = Convert.ToString(objStaticItem.GetValue(objSalesItem, null));
}
else
{
var objDynamicItem = objSalesItem.FinanceColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]);
if (objDynamicItem != null)
{
value = objDynamicItem.Value;
}
}
cell.SetCellValue(value);
}
}
}
}
string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports");
string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory));
if (!Directory.Exists(fileUri))
{
Directory.CreateDirectory(fileUri);
}
string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now),
extension = "xlsx";
string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension));
int count = 1;
isExist:
if (File.Exists(filePath))
{
fileName = string.Format("{0}{1}{2}", fileName, count, extension);
filePath = Path.Combine(fileUri, fileName);
count++;
goto isExist;
}
using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension));
}
return(Ok <DataResponse>(response));
#endregion
}
#region Sales Grouped Report
return(ExportGroupedSalesToExcel(filter));
#endregion
}
catch (Exception ex)
{
ex.Log();
}
response.Message = "Internal Server Error";
return(Ok <DataResponse>(response));
}
public void TestXSSFTextParagraph()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/ "Arial");
rt.ApplyFont(font);
shape.SetText(/*setter*/ rt);
Assert.IsNotNull(shape.GetCTShape());
Assert.IsNotNull(shape.GetEnumerator());
Assert.IsNotNull(XSSFSimpleShape.GetPrototype());
foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber)))
{
shape.TextParagraphs[(0)].SetBullet(nr);
Assert.IsNotNull(shape.Text);
}
shape.TextParagraphs[(0)].SetBullet(false);
Assert.IsNotNull(shape.Text);
shape.SetText("testtext");
Assert.AreEqual("testtext", shape.Text);
shape.SetText(new XSSFRichTextString());
Assert.AreEqual("null", shape.Text);
shape.AddNewTextParagraph();
shape.AddNewTextParagraph("test-other-text");
shape.AddNewTextParagraph(new XSSFRichTextString("rtstring"));
shape.AddNewTextParagraph(new XSSFRichTextString());
Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.CLIP);
Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.OVERFLOW);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.CLIP);
Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.OVERFLOW);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
Assert.AreEqual((short)VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.Bottom;
Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.Top;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.STACKED);
Assert.AreEqual(TextDirection.STACKED, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.HORIZONTAL);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ 12.32);
Assert.AreEqual(12.32, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ 12.31);
Assert.AreEqual(12.31, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ 13.31);
Assert.AreEqual(13.31, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ 23.31);
Assert.AreEqual(23.31, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
Assert.IsTrue(shape.WordWrap);
shape.WordWrap = (/*setter*/ false);
Assert.IsFalse(shape.WordWrap);
shape.WordWrap = (/*setter*/ true);
Assert.IsTrue(shape.WordWrap);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.NORMAL);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.SHAPE);
Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.NONE);
Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit);
Assert.AreEqual(5, shape.ShapeType);
shape.ShapeType = (/*setter*/ 23);
Assert.AreEqual(23, shape.ShapeType);
// TODO: should this be supported?
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
Assert.IsNotNull(shape.GetShapeProperties());
}
finally
{
wb.Close();
}
}
/**
* @param stabilityClassifier used to optimise caching performance. Pass <code>null</code>
* for the (conservative) assumption that any cell may have its defInition Changed After
* Evaluation begins.
* @deprecated (Sep 2009) (reduce overloading) use {@link #Create(XSSFWorkbook, NPOI.ss.formula.IStabilityClassifier, NPOI.ss.formula.udf.UDFFinder)}
*/
public XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier)
{
_bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, null);
_book = workbook;
}
public static System.IO.MemoryStream createExcelReport(DataTable dt)
{
using (System.IO.MemoryStream mem = new System.IO.MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.Center;
headerLabelCellStyle.BorderBottom = BorderStyle.Thin;
headerLabelCellStyle.BorderRight = BorderStyle.Thin;
headerLabelCellStyle.BorderTop = BorderStyle.Thin;
headerLabelCellStyle.BorderLeft = BorderStyle.Thin;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.Bold;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.Right;
rightAlignedCellStyle.BorderBottom = BorderStyle.Thin;
rightAlignedCellStyle.BorderRight = BorderStyle.Thin;
rightAlignedCellStyle.BorderTop = BorderStyle.Thin;
rightAlignedCellStyle.BorderLeft = BorderStyle.Thin;
#endregion
#region Currency Cell Style
var CellStyleRight = workbook.CreateCellStyle();
CellStyleRight.Alignment = HorizontalAlignment.Right;
CellStyleRight.BorderBottom = BorderStyle.Thin;
CellStyleRight.BorderRight = BorderStyle.Thin;
CellStyleRight.BorderTop = BorderStyle.Thin;
CellStyleRight.BorderLeft = BorderStyle.Thin;
#endregion
#region Detail Currency Subtotal Style
var CellStyleNormal = workbook.CreateCellStyle();
CellStyleNormal.BorderBottom = BorderStyle.Thin;
CellStyleNormal.BorderRight = BorderStyle.Thin;
CellStyleNormal.BorderTop = BorderStyle.Thin;
CellStyleNormal.BorderLeft = BorderStyle.Thin;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.Normal;
CellStyleNormal.SetFont(detailCurrencySubtotalFont);
#endregion
#endregion
var sheet = workbook.CreateSheet(dt.TableName);
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
List <String> columns = new List <string>();
for (int i = 0; i < dt.Columns.Count; i++)
{
columns.Add(dt.Columns[i].ColumnName);
var cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
cell.CellStyle = headerLabelCellStyle;
}
rowIndex++;
//// Add data rows
foreach (System.Data.DataRow item in dt.Rows)
{
row = sheet.CreateRow(rowIndex);
for (int ii = 0; ii < dt.Columns.Count; ii++)
{
var cell = row.CreateCell(ii);
cell.SetCellValue(dt.Columns[ii].ColumnName);
if (dt.Columns[ii].DataType == typeof(System.Decimal))
{
cell.CellStyle = CellStyleRight;
}
else
{
cell.CellStyle = CellStyleNormal;
}
cell.SetCellValue(item[dt.Columns[ii].ColumnName].ToString());
}
rowIndex++;
}
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(mem);
return(mem);
}
}
/// <summary>
/// Загрузка из XSLX.
/// </summary>
/// <param name="FileName">Имя файла с полным путем к XLSX файлу</param>
private void LoadXLSX(string FileName)
{
BeginOfLoad();
DateTime dateTime1 = DateTime.Now;
NPOI.XSSF.UserModel.XSSFWorkbook book = null; //Книга.
NPOI.SS.UserModel.ISheet sheet = null; //Лист.
try
{
FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
if (fs != null)
{
fs.Close();
}
}
catch (Exception ex)
{
sys.SM("Ошибка открытия файла шаблона: " + ex.Message);
return;
}
//Устанавливаем текущий лист.
string errorMes = "";
if (!sys.SetCurrentSheet(book, 1, true, out sheet, out errorMes))
{
return;
}
string[] arrLine = null; //= new string[sheet.LastCellNum + 1];
int columnsCount = 0;
//Код выполняется в отдельном потоке
var task1 = Task.Factory.StartNew(() =>
{
//запускаем цикл по строкам
for (int row = sheet.FirstRowNum; row <= sheet.LastRowNum; row++)
{
//получаем строку
var currentRow = sheet.GetRow(row);
if (currentRow == null)
{
continue; //null когда строка содержит только пустые ячейки
}
//запускаем цикл по столбцам
if (arrLine == null)
{
columnsCount = currentRow.LastCellNum;
arrLine = new string[currentRow.LastCellNum];
}
for (int col = 0; col < columnsCount; col++)
{
if (loadStop)
{
return;
}
//получаем значение ячейки
string value = "";
var currentCell = currentRow.GetCell(col);
if (currentCell == null)
{
arrLine[col] = value;
continue;
}
NPOI.SS.UserModel.CellType ctype = currentCell.CellType;
if (ctype == NPOI.SS.UserModel.CellType.String)
{
value = currentCell.StringCellValue;
}
else if (ctype == NPOI.SS.UserModel.CellType.Boolean)
{
value = currentCell.BooleanCellValue.ToString();
}
else if (ctype == NPOI.SS.UserModel.CellType.Formula)
{
value = currentCell.CellFormula;
}
else if (ctype == NPOI.SS.UserModel.CellType.Numeric)
{
value = currentCell.NumericCellValue.ToString();
}
else if (ctype == NPOI.SS.UserModel.CellType.Error)
{
value = currentCell.ErrorCellValue.ToString();
}
arrLine[col] = value;
}
if (row == sheet.FirstRowNum)
{
for (int i = 0; i < arrLine.Length; i++)
{
if (arrLine[i] == "")
{
arrLine[i] = "Column";
}
}
Arr.SetUniqValue(arrLine); //Меняем название колонок, если они дублируются.
//Потому чтонельзя добавлять в dt колонки с одинаковым именем.
for (int i = 0; i < arrLine.Length; i++)
{
dt.Columns.Add(arrLine[i], typeof(string));
}
}
else
{
dt.Rows.Add(arrLine);
}
}
});
//После завершения обновляем GUI компаненты.
var task2 = task1.ContinueWith((previous) =>
{
EndOfLoad(dateTime1);
}, TaskScheduler.FromCurrentSynchronizationContext());
return;
}
/**
* @param stabilityClassifier used to optimise caching performance. Pass <code>null</code>
* for the (conservative) assumption that any cell may have its defInition Changed After
* Evaluation begins.
* @param udfFinder pass <code>null</code> for default (AnalysisToolPak only)
*/
public static XSSFFormulaEvaluator Create(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)
{
return(new XSSFFormulaEvaluator(workbook, stabilityClassifier, udfFinder));
}
public XSSFFormulaEvaluator(XSSFWorkbook workbook)
: this(workbook, null, null)
{
}
public void CreateExcelFile()
{
string FileName = @"d:\5.5.5.101_5555_Log_18.txt";
string[] filelist = File.ReadAllLines(FileName, Encoding.Default);
List <string> StringLists = new List <string>();
int gg = 0;
for (int linenum = filelist.Length - 1; linenum >= 0; linenum--)
{
if (filelist[linenum].IndexOf("ANR") > -1)
{
int first = filelist[linenum].IndexOf("ANR in ") + "ANR in ".Length;
int last = filelist[linenum].LastIndexOf(" (");
string str2 = filelist[linenum].Substring(first, last - first);
StringLists.Add(str2);
}
}
////建立Excel 2007檔案
IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
//合併區
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 0, 6)); //合併Summary行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 0, 5)); //合併Error List行
//背景色（藍色）
ICellStyle cellStyle0 = workbook.CreateCellStyle();
cellStyle0.FillPattern = FillPattern.SolidForeground;
cellStyle0.FillForegroundColor = IndexedColors.PaleBlue.Index;
cellStyle0.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（綠色）
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle1.FillForegroundColor = IndexedColors.Lime.Index;
cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（粉色）
ICellStyle cellStyle2 = workbook.CreateCellStyle();
cellStyle2.FillPattern = FillPattern.SolidForeground;
cellStyle2.FillForegroundColor = IndexedColors.Tan.Index;
cellStyle2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（灰色）
ICellStyle cellStyle3 = workbook.CreateCellStyle();
cellStyle3.FillPattern = FillPattern.SolidForeground;
cellStyle3.FillForegroundColor = IndexedColors.Grey25Percent.Index;
//背景色（白色）
ICellStyle cellStyle4 = workbook.CreateCellStyle();
cellStyle4.FillPattern = FillPattern.SolidForeground;
cellStyle4.FillForegroundColor = IndexedColors.White.Index;
//Summary儲存格格式
ICellStyle summaryStyle = workbook.CreateCellStyle();
IFont summaryFont = workbook.CreateFont();
summaryFont.FontHeightInPoints = 18;
summaryStyle.SetFont(summaryFont);
summaryStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
summaryStyle.FillPattern = FillPattern.SolidForeground;
summaryStyle.FillForegroundColor = IndexedColors.PaleBlue.Index;
//A列
sheet.CreateRow(0).CreateCell(0).SetCellValue("Project Name");
sheet.CreateRow(1).CreateCell(0).SetCellValue("Model Name");
sheet.CreateRow(2).CreateCell(0).SetCellValue("Start Time");
sheet.CreateRow(3).CreateCell(0).SetCellValue("Renew Time");
sheet.CreateRow(4).CreateCell(0).SetCellValue("SW Build Time");
sheet.CreateRow(5).CreateCell(0).SetCellValue("Project No.");
sheet.CreateRow(6).CreateCell(0).SetCellValue("Test Device");
sheet.CreateRow(7).CreateCell(0).SetCellValue("Tester");
for (int A = 0; A < 8; A++)
{
sheet.GetRow(A).GetCell(0).CellStyle = cellStyle0;
}
//E列
sheet.GetRow(0).CreateCell(4).SetCellValue("Date");
sheet.GetRow(1).CreateCell(4).SetCellValue("Period (H)");
sheet.GetRow(2).CreateCell(4).SetCellValue("SW ISSUES");
sheet.GetRow(3).CreateCell(4).SetCellValue("System Crash");
sheet.GetRow(4).CreateCell(4).SetCellValue("Result");
sheet.GetRow(5).CreateCell(4).SetCellValue("MTBF_SW");
sheet.GetRow(6).CreateCell(4).SetCellValue("MTBF_Crash");
for (int E = 0; E < 7; E++)
{
sheet.GetRow(E).GetCell(4).CellStyle = cellStyle0;
}
sheet.GetRow(4).GetCell(4).CellStyle = cellStyle4;
//F列
sheet.GetRow(0).CreateCell(5).SetCellValue("-----");
sheet.GetRow(1).CreateCell(5).SetCellValue("-----");
sheet.GetRow(2).CreateCell(5).SetCellValue("-----");
sheet.GetRow(3).CreateCell(5).SetCellValue("-----");
sheet.GetRow(4).CreateCell(5).SetCellValue("");
sheet.GetRow(5).CreateCell(5).SetCellValue("-----");
sheet.GetRow(6).CreateCell(5).SetCellValue("-----");
for (int F = 0; F < 7; F++)
{
sheet.GetRow(F).GetCell(5).CellStyle = cellStyle2;
}
sheet.GetRow(4).GetCell(5).CellStyle = cellStyle4;
//Summary
sheet.CreateRow(9).CreateCell(0).SetCellValue("Summary");
sheet.GetRow(9).GetCell(0).CellStyle = summaryStyle;
//Error List
sheet.CreateRow(10).CreateCell(0).SetCellValue("Error List");
sheet.GetRow(10).GetCell(0).CellStyle = cellStyle3;
//Total
sheet.GetRow(10).CreateCell(6).SetCellValue("Total");
sheet.GetRow(10).GetCell(6).CellStyle = cellStyle3;
//搜尋相同字串並記次
Dictionary <string, int> dict = new Dictionary <string, int>();
foreach (string myStringList in StringLists)
{
if (dict.ContainsKey(myStringList))
{
//如果Dictionary中存在这个关键词元素，则把这个Dictionary的key+1
dict[myStringList]++;
}
else
{
//如果Dictionary中不存在这个关键词元素，则把它添加进Dictionary
dict.Add(myStringList, 1);
}
}
int rowcnt = dict.Count;
while (rowcnt != 0)
{
foreach (KeyValuePair <string, int> item in dict)
{
Console.WriteLine(item.Key);
Console.WriteLine(item.Value);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10 + rowcnt, 10 + rowcnt, 0, 5)); //合併Error List行
sheet.CreateRow(10 + rowcnt).CreateCell(0).SetCellValue(item.Key);
sheet.GetRow(10 + rowcnt).CreateCell(6).SetCellValue(item.Value);
rowcnt--;
}
}
for (int c = 0; c <= 25; c++)
{
sheet.AutoSizeColumn(c);
}
FileStream file = new FileStream(@"d:\npoi.xlsx", FileMode.Create);//產生檔案
workbook.Write(file);
file.Close();
}
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="exceltype">是否模板</param>
/// <param name="FileName">文件名称</param>
public void DataTableToExcelTemplet(DataTable dtSource, string ExportType, string FileName = "")
{
string TempletFileName = "";//模板文件名称
FileStream file = null;
NPOI.SS.UserModel.IWorkbook workbook = null;
SaveFileDialog savefd = new SaveFileDialog();
//模板文件
TempletFileName = Application.StartupPath + "\\JNMLTemp.xls";
file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
if (FileName != "")
{
savefd.FileName = FileName + ".xls";
}
savefd.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (savefd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
if (savefd.FilterIndex == 1)
{
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
}
else
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
}
HSSFSheet sheet = (HSSFSheet)workbook.GetSheet("Sheet1");
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 1;
foreach (DataRow row in dtSource.Rows)
{
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.GetRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.GetCell(column.Ordinal);
string drValue = row[column].ToString();
if (column.ColumnName == "numxh")
{
newCell.SetCellValue(rowIndex.ToString());
continue;
}
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
using (FileStream fs = new FileStream(savefd.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
if (file != null)
{
file.Dispose();
file.Close();
}
}
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="FileName">文件名称</param>
/// <param name="exceltype">是否模板</param>
public void DataTableToExcel(DataTable dtSource, string ExportType, string strHeaderText, string FileName = "")
{
NPOI.SS.UserModel.IWorkbook workbook = null;
SaveFileDialog savefd = new SaveFileDialog();
if (FileName != "")
{
savefd.FileName = FileName + ".xls";
}
savefd.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (savefd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
if (savefd.FilterIndex == 1)
{
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(ExportType);
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = SetExcelColWidth(dtSource, ExportType);
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet(ExportType);
}
#region 表头及样式
{
if (strHeaderText != "")
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 18;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
rowIndex = 1;
}
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 11;
font.Boldweight = 700;
font.IsBold = true;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex++;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
using (FileStream fs = new FileStream(savefd.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="isColumnName">第一行是否是列名</param>
/// <returns>返回datatable</returns>
public static DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumnHeader, string fileName)
{
ISheet sheet = null;
IWorkbook workbook = null;
System.Data.DataTable data = new System.Data.DataTable();
FileStream fs = null;
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
}
catch (Exception ex)
{
CloseStream(fs);
MessageBox.Show("文件打开失败！请检查文件是否被占用，关闭已打开文件后重试！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(data);
}
try
{
if (Path.GetExtension(fileName) == ".xlsx")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);//2007
}
else if (Path.GetExtension(fileName) == ".xls")
{
fs.Position = 0;
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);//2003
}
if (workbook == null)
{
CloseStream(fs);
MessageBox.Show($"打开文件失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(data);
}
}
catch (Exception ex)
{
CloseStream(fs);
MessageBox.Show($"{ex.Message}", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
//Aspose2Data(fileName);
return(data);
}
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet == null)
{
MessageBox.Show($"未查询到Sheet表格！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
CloseStream(fs);
return(data);
}
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
try
{
if (isFirstRowColumnHeader)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
DataColumn column = new DataColumn("column" + (i + 1));
data.Columns.Add(column);
}
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
bool IsStrNull = false;
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
if (row.GetCell(j).ToString().Trim() != "")
{
dataRow[j] = row.GetCell(j).ToString();
IsStrNull = true;
}
}
}
if (IsStrNull)
{
data.Rows.Add(dataRow);
}
}
}
catch (Exception ex)
{
CloseStream(fs);
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(data);
}
CloseStream(fs);
return(data);
}
public IHttpActionResult ExportGroupedSalesToExcel(FilterSales filter)
{
var response = new DataResponse <string>();
try
{
#region Sales Default Export
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport");
NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper();
string[] headers = new string[] { filter.GroupBy == 1 ? "Practice Name" : filter.GroupBy == 2 ? "Rep Name" : "Sales Team", "Sales", "Last Activity On" };
var headerRow = sheet.CreateRow(0);
//create header
for (int i = 0; i < headers.Length; i++)
{
sheet.DefaultColumnWidth = 20;
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor colorToFill = new XSSFColor(Color.Aqua);
style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua);
headerRow.RowStyle = style;
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var dataResponse = repository.GetAllGroupedSales(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath);
List <GroupedSales> salesList = dataResponse.Model.List.ToList();
int recordCount = dataResponse.Model.Pager.TotalCount;
//fill content
var rowIndex = 0;
for (int i = 0; i < recordCount; i++)
{
rowIndex++;
var row = sheet.CreateRow(rowIndex);
var objSalesItem = salesList[i];
if (objSalesItem == null)
{
continue;
}
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var cell1 = row.CreateCell(0);
cell1.CellStyle = workbook.CreateCellStyle();
cell1.CellStyle.SetFont(font);
cell1.SetCellValue(string.IsNullOrEmpty(objSalesItem.KeyName) ? "Missing Information" : objSalesItem.KeyName);
var cell2 = row.CreateCell(1);
cell2.CellStyle = workbook.CreateCellStyle();
cell2.CellStyle.SetFont(font);
cell2.SetCellValue(objSalesItem.Count);
var cell3 = row.CreateCell(2);
cell3.CellStyle = workbook.CreateCellStyle();
cell3.CellStyle.SetFont(font);
cell3.SetCellValue(Convert.ToString(objSalesItem.LastActivityOn));
}
string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports");
string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory));
if (!Directory.Exists(fileUri))
{
Directory.CreateDirectory(fileUri);
}
string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now),
extension = "xlsx";
string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension));
int count = 1;
isExist:
if (File.Exists(filePath))
{
fileName = string.Format("{0}{1}{2}", fileName, count, extension);
filePath = Path.Combine(fileUri, fileName);
count++;
goto isExist;
}
using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension));
}
return(Ok <DataResponse>(response));
#endregion
}
catch (Exception ex)
{
ex.Log();
}
response.Message = "Internal Server Error";
return(Ok <DataResponse>(response));
}
/// <summary>
/// 匯入副檔名為.xls的Excel檔th
/// </summary>
private DataTable ImportXLS(string file)
{
DataTable dt = new DataTable();
IWorkbook workbook;
//只能讀取 System.IO.Stream
//FileContent 屬性會取得指向要上載之檔案的 Stream 物件。這個屬性可以用於存取檔案的內容 (做為位元組)。
//例如，您可以使用 FileContent 屬性傳回的 Stream 物件，將檔案的內容做為位元組進行讀取並將其以位元組陣列儲存。
//FileContent 屬性，型別：System.IO.Stream
//檢查上傳檔案的附檔名
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 適用XLSX格式，HSSFWorkbook 適用XLS格式
if (fileExt == ".xlsx")
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null)
{
return(null);
}
//取得目標sheet,指定為第1個分頁
ISheet sheet = workbook.GetSheetAt(0);
//取得表頭
IRow header = sheet.GetRow(sheet.FirstRowNum);
List <int> columns = new List <int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
{
dt.Columns.Add(new DataColumn(obj.ToString()));
}
columns.Add(i);
}
//取得資料內容
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return(dt);
}
// Disabled because shift rows is not yet implemented for SXSSFWorkbook
public void disabled_testBug53798XLSXStream()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53798_ShiftNegative_TMPL.xlsx");
FileInfo xlsOutput = TempFile.CreateTempFile("testBug53798", ".xlsx");
//bug53798Work(new SXSSFWorkbook(wb), xlsOutput);
}
private XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder)
{
_bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), stabilityClassifier, udfFinder);
_book = workbook;
}
public void TestCreate()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;
String[] urls =
{
"http://apache.org/",
"www.apache.org",
"/temp",
"file:///c:/temp",
"http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"
};
for (int i = 0; i < urls.Length; i++)
{
String s = urls[i];
XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
link.Address = (s);
XSSFCell cell = row.CreateCell(i) as XSSFCell;
cell.Hyperlink = (link);
}
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
// Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
}
public void TestLoadSave()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithMoreVariousData.xlsx");
ICreationHelper CreateHelper = workbook.GetCreationHelper();
Assert.AreEqual(3, workbook.NumberOfSheets);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
// Check hyperlinks
Assert.AreEqual(4, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
// Write out, and check
// Load up again, check all links still there
XSSFWorkbook wb2 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
Assert.AreEqual(3, wb2.NumberOfSheets);
Assert.IsNotNull(wb2.GetSheetAt(0));
Assert.IsNotNull(wb2.GetSheetAt(1));
Assert.IsNotNull(wb2.GetSheetAt(2));
sheet = (XSSFSheet)wb2.GetSheetAt(0);
// Check hyperlinks again
Assert.AreEqual(4, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
// Add one more, and re-check
IRow r17 = sheet.CreateRow(17);
ICell r17c = r17.CreateCell(2);
IHyperlink hyperlink = CreateHelper.CreateHyperlink(HyperlinkType.Url);
hyperlink.Address = ("http://poi.apache.org/spreadsheet/");
hyperlink.Label = "POI SS Link";
r17c.Hyperlink = (hyperlink);
Assert.AreEqual(5, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
Assert.AreEqual(HyperlinkType.Url,
sheet.GetRow(17).GetCell(2).Hyperlink.Type);
Assert.AreEqual("POI SS Link",
sheet.GetRow(17).GetCell(2).Hyperlink.Label);
Assert.AreEqual("http://poi.apache.org/spreadsheet/",
sheet.GetRow(17).GetCell(2).Hyperlink.Address);
// Save and re-load once more
XSSFWorkbook wb3 = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb2);
Assert.AreEqual(3, wb3.NumberOfSheets);
Assert.IsNotNull(wb3.GetSheetAt(0));
Assert.IsNotNull(wb3.GetSheetAt(1));
Assert.IsNotNull(wb3.GetSheetAt(2));
sheet = (XSSFSheet)wb3.GetSheetAt(0);
Assert.AreEqual(5, sheet.NumHyperlinks);
doTestHyperlinkContents(sheet);
Assert.AreEqual(HyperlinkType.Url,
sheet.GetRow(17).GetCell(2).Hyperlink.Type);
Assert.AreEqual("POI SS Link",
sheet.GetRow(17).GetCell(2).Hyperlink.Label);
Assert.AreEqual("http://poi.apache.org/spreadsheet/",
sheet.GetRow(17).GetCell(2).Hyperlink.Address);
}
public void Bug49702()
{
// First try with a new file
XSSFWorkbook wb1 = new XSSFWorkbook();
// Should have one style
Assert.AreEqual(1, wb1.NumCellStyles);
wb1.GetCellStyleAt((short)0);
try
{
wb1.GetCellStyleAt((short)1);
Assert.Fail("Shouldn't be able to get style at 1 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Add another one
ICellStyle cs = wb1.CreateCellStyle();
cs.DataFormat = ((short)11);
// Re-check
Assert.AreEqual(2, wb1.NumCellStyles);
wb1.GetCellStyleAt((short)0);
wb1.GetCellStyleAt((short)1);
try
{
wb1.GetCellStyleAt((short)2);
Assert.Fail("Shouldn't be able to get style at 2 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Save and reload
XSSFWorkbook nwb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb1);
Assert.AreEqual(2, nwb.NumCellStyles);
nwb.GetCellStyleAt((short)0);
nwb.GetCellStyleAt((short)1);
try
{
nwb.GetCellStyleAt((short)2);
Assert.Fail("Shouldn't be able to Get style at 2 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
// Now with an existing file
XSSFWorkbook wb2 = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx");
Assert.AreEqual(3, wb2.NumCellStyles);
wb2.GetCellStyleAt((short)0);
wb2.GetCellStyleAt((short)1);
wb2.GetCellStyleAt((short)2);
try
{
wb2.GetCellStyleAt((short)3);
Assert.Fail("Shouldn't be able to Get style at 3 that doesn't exist");
}
catch (ArgumentOutOfRangeException) { }
wb2.Close();
wb1.Close();
nwb.Close();
}
public void Bug58245_XSSFSheetIterator()
{
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet();
// =====================================================================
// Case 1: Existing code uses XSSFSheet for-each loop
// =====================================================================
// Original code (no longer valid)
/*
* for (XSSFSheet sh : wb) {
* sh.createRow(0);
* }
*/
// Option A:
foreach (XSSFSheet sh in wb)
{
sh.CreateRow(0);
}
// Option B (preferred for new code):
foreach (ISheet sh in wb)
{
sh.CreateRow(0);
}
// =====================================================================
// Case 2: Existing code creates an iterator variable
// =====================================================================
// Original code (no longer valid)
/*
* Iterator<XSSFSheet> it = wb.iterator();
* XSSFSheet sh = it.next();
* sh.createRow(0);
*/
// Option A:
{
IEnumerator <XSSFSheet> it = wb.GetEnumerator() as IEnumerator <XSSFSheet>;
XSSFSheet sh = it.Current;
sh.CreateRow(0);
}
// Option B:
{
//IEnumerator<XSSFSheet> it = wb.XssfSheetIterator();
//XSSFSheet sh = it.Current;
//sh.CreateRow(0);
}
// Option C (preferred for new code):
{
IEnumerator <ISheet> it = wb.GetEnumerator() as IEnumerator <ISheet>;
ISheet sh = it.Current;
sh.CreateRow(0);
}
wb.Close();
}
public void SaveLoadNew()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
//check that the default date system is Set to 1900
CT_WorkbookPr pr = wb1.GetCTWorkbook().workbookPr;
Assert.IsNotNull(pr);
Assert.IsTrue(pr.IsSetDate1904());
Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");
ISheet sheet1 = wb1.CreateSheet("sheet1");
ISheet sheet2 = wb1.CreateSheet("sheet2");
wb1.CreateSheet("sheet3");
IRichTextString rts = wb1.GetCreationHelper().CreateRichTextString("hello world");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
sheet2.CreateRow(0);
Assert.AreEqual(0, wb1.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, wb1.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(2).LastRowNum);
FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
Stream out1 = File.OpenWrite(file.FullName);
wb1.Write(out1);
out1.Close();
// Check the namespace Contains what we'd expect it to
OPCPackage pkg = OPCPackage.Open(file.ToString());
PackagePart wbRelPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));
Assert.IsNotNull(wbRelPart);
Assert.IsTrue(wbRelPart.IsRelationshipPart);
Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared strings and styles
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(5, wbPart.Relationships.Size);
wb1.Close();
// Load back the XSSFWorkbook
XSSFWorkbook wb2 = new XSSFWorkbook(pkg);
Assert.AreEqual(3, wb2.NumberOfSheets);
Assert.IsNotNull(wb2.GetSheetAt(0));
Assert.IsNotNull(wb2.GetSheetAt(1));
Assert.IsNotNull(wb2.GetSheetAt(2));
Assert.IsNotNull(wb2.GetSharedStringSource());
Assert.IsNotNull(wb2.GetStylesSource());
Assert.AreEqual(0, wb2.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, wb2.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(2).LastRowNum);
sheet1 = wb2.GetSheetAt(0);
Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);
pkg.Close();
Assert.AreEqual(0, Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "*.tmp").Length, "At Last: There are no temporary files.");
}
/**
* Creates an XSSFName object - called internally by XSSFWorkbook.
*
* @param name - the xml bean that holds data represenring this defined name.
* @param workbook - the workbook object associated with the name
* @see NPOI.XSSF.usermodel.XSSFWorkbook#CreateName()
*/
public XSSFName(CT_DefinedName name, XSSFWorkbook workbook)
{
_workbook = workbook;
_ctName = name;
}
protected XSSFEvaluationWorkbook(IWorkbook book)
{
_uBook = (XSSFWorkbook)book;
}
private LoadPatternList()
{
using (FileStream fs = new FileStream(path,FileMode.Open,FileAccess.Read))
{
IWorkbook workbook = new XSSFWorkbook(fs);
int sheetnumber = workbook.NumberOfSheets;
int [][][] sheetelements = new int[sheetnumber][][];
for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){
ISheet _isheet = workbook.GetSheetAt(sheetindex);
int lastrownum = _isheet.LastRowNum;
int [][] rowelements = new int[lastrownum+1][];
for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){
IRow row = _isheet.GetRow(rowindex);
if(row == null)continue;
int lastcellnum = row.LastCellNum;
int[] cellelements = new int[lastcellnum+1];
for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){
ICell cell = row.GetCell(cellindex);
if(cell != null){
cellelements[cellindex] = Convert.ToInt32(cell.ToString());
}
}
rowelements[rowindex] = cellelements;
}
sheetelements[sheetindex] = rowelements;
}
data = sheetelements;
}
if(data.Length > 0){
checksign = "No";
}
}
public static DataSet CreateDataSetFromExcel(Stream streamToProcess, string fileExtentison = "xlsx")
{
DataSet model = new DataSet();
if (streamToProcess != null)
{
if (fileExtentison.ToLower() == "xlsx")
{
XSSFWorkbook workbook = new XSSFWorkbook(streamToProcess);
model = ProcessXLSX(workbook);
}
else
{
HSSFWorkbook workbook = new HSSFWorkbook(streamToProcess);
model = ProcessXLSX(workbook);
}
}
return model;
}
