private void ComposeXLSX() { XSSFWorkbook outbook = new XSSFWorkbook(); outbook.CreateSheet("Coordinates"); var Sheet = outbook.GetSheetAt(0); var Row = Sheet.CreateRow(0); ICell Cell = Row.CreateCell(0); Cell.SetCellValue("Name"); Cell = Row.CreateCell(1); Cell.SetCellValue("X"); Cell = Row.CreateCell(2); Cell.SetCellValue("Y"); Cell = Row.CreateCell(3); Cell.SetCellValue("Z"); int rnum = 1; if (Graph.Count == 0) { foreach (NodePoint node in AllPoints) { Row = Sheet.CreateRow(rnum++); Cell = Row.CreateCell(0); Cell.SetCellType(CellType.String); Cell.SetCellValue(node.Name); Cell = Row.CreateCell(1); Cell.SetCellValue(Math.Round(node.X, 3)); Cell = Row.CreateCell(2); Cell.SetCellValue(Math.Round(node.Y, 3)); Cell = Row.CreateCell(3); Cell.SetCellValue(Math.Round(node.Z, 3)); } } else { for (LinkedListNode <NodePoint> node = Graph.First; node != null; node = node.Next) { Row = Sheet.CreateRow(rnum++); Cell = Row.CreateCell(0); Cell.SetCellType(CellType.String); Cell.SetCellValue(node.Value.Name); Cell = Row.CreateCell(1); Cell.SetCellValue(Math.Round(node.Value.X, 3)); Cell = Row.CreateCell(2); Cell.SetCellValue(Math.Round(node.Value.Y, 3)); Cell = Row.CreateCell(3); Cell.SetCellValue(Math.Round(node.Value.Z, 3)); } } var myfont = outbook.CreateFont(); myfont.FontHeightInPoints = 11; myfont.FontName = "Times New Roman"; var myStyle = outbook.CreateCellStyle(); myStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; myStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; myStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; myStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; myStyle.SetFont(myfont); myStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; myStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; for (int i = 0; i < Sheet.PhysicalNumberOfRows; i++) { Row = Sheet.GetRow(i); for (int j = 0; j <= 3; j++) { Cell = Row.GetCell(j); Cell.CellStyle = myStyle; } } SaveFileDialog myDialog = new SaveFileDialog(); myDialog.Filter = "Документы Excel (*.xlsx)|**.XLSX"; string f = ""; f = CATIA.ActiveDocument.get_Name(); myDialog.FileName = f + "_" + "XYZ" + ".xlsx"; if (myDialog.ShowDialog() == true) { var FileName = myDialog.FileName; var outFile = new FileStream(FileName, FileMode.Create, FileAccess.Write); outbook.Write(outFile); } }
private TableDefinition convertToDataTable(string fileName) { XSSFWorkbook workbook; using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(file); } ISheet sheet = workbook.GetSheetAt(0); ICell cellKey; ICell cellValue; ICell cell; List <string> columns = new List <string>(); var tableDef = new TableDefinition(); var dt = new TableDefinitionDataSet.FieldDefinitionDataTable(); IEnumerator rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { IRow row = (XSSFRow)rows.Current; cellKey = row.GetCell(1); if (cellKey.ToString().Equals("Table Name", StringComparison.OrdinalIgnoreCase)) { cellValue = row.GetCell(2); tableDef.TableName = cellValue.ToString(); tableDef.SystemName = tableDef.TableName.Substring(0, tableDef.TableName.IndexOf('_')); continue; } else if (cellKey.ToString().Equals("Table Description", StringComparison.OrdinalIgnoreCase)) { cellValue = row.GetCell(2); tableDef.TableDesc = cellValue.ToString(); continue; } else if (cellKey.ToString().Equals("Column Prefix", StringComparison.OrdinalIgnoreCase)) { cellValue = row.GetCell(2); tableDef.ColumnPrifix = cellValue.ToString(); continue; } else if (cellKey.ToString().Equals("ShortName", StringComparison.OrdinalIgnoreCase)) { //欄位標題 for (int i = 1; i < row.LastCellNum; i++) { cell = row.GetCell(i); columns.Add(cell.ToString()); } continue; } else if (cellKey.ToString() == "") { continue; } var dr = dt.NewFieldDefinitionRow(); for (int i = 0; i < columns.Count; i++) { if (!dt.Columns.Contains(columns[i])) { continue; } cell = row.GetCell(i + 1); if (cell == null) { dr[columns[i]] = DBNull.Value; } else { cell.SetCellType(CellType.String); dr[columns[i]] = cell.StringCellValue; //dr[columns[i]] = cell.ToString(); } } dt.AddFieldDefinitionRow(dr); } dt.AcceptChanges(); tableDef.Table = dt; return(tableDef); }
public void TestAddNewTextParagraphWithRTS() { XSSFWorkbook wb1 = new XSSFWorkbook(); XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet; XSSFDrawing drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; XSSFTextBox shape = drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)); XSSFRichTextString rt = new XSSFRichTextString("Test Rich Text String"); XSSFFont font = wb1.CreateFont() as XSSFFont; font.SetColor(new XSSFColor(Color.FromRgb(0, 255, 255))); font.FontName = ("Arial"); rt.ApplyFont(font); XSSFFont midfont = wb1.CreateFont() as XSSFFont; midfont.SetColor(new XSSFColor(Color.FromRgb(0, 255, 0))); rt.ApplyFont(5, 14, midfont); // Set the text "Rich Text" to be green and the default font XSSFTextParagraph para = shape.AddNewTextParagraph(rt); // Save and re-load it XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook; wb1.Close(); sheet = wb2.GetSheetAt(0) as XSSFSheet; // Check drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing; List <XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(1, shapes.Count); Assert.IsTrue(shapes[0] is XSSFSimpleShape); XSSFSimpleShape sshape = (XSSFSimpleShape)shapes[0]; List <XSSFTextParagraph> paras = sshape.TextParagraphs; Assert.AreEqual(2, paras.Count); // this should be 2 as XSSFSimpleShape Creates a default paragraph (no text), and then we add a string to that. List <XSSFTextRun> runs = para.TextRuns; Assert.AreEqual(3, runs.Count); // first run properties Assert.AreEqual("Test ", runs[0].Text); Assert.AreEqual("Arial", runs[0].FontFamily); var clr = runs[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 255 }, new int[] { clr.R, clr.G, clr.B })); // second run properties Assert.AreEqual("Rich Text", runs[1].Text); Assert.AreEqual(XSSFFont.DEFAULT_FONT_NAME, runs[1].FontFamily); clr = runs[1].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 0 }, new int[] { clr.R, clr.G, clr.B })); // third run properties Assert.AreEqual(" String", runs[2].Text); Assert.AreEqual("Arial", runs[2].FontFamily); clr = runs[2].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 255 }, new int[] { clr.R, clr.G, clr.B })); checkRewrite(wb2); wb2.Close(); }
/// <summary> /// 导入数据 /// </summary> /// <param name="fileExtension"></param> /// <param name="filePath"></param> /// <returns></returns> public SavedResult <Int64> ImportData(string fileExtension, string filePath) { SavedResult <Int64> result = new SavedResult <Int64>(); IList <QtOrgDygxModel> qtOrgDygxes = new List <QtOrgDygxModel>(); List <string> xmorglist = new List <string>(); //数据库的所有对象的项目库组织代码的list string message = ""; //存储重复的项目库组织代码 Dictionary <string, object> dicwhere = new Dictionary <string, object>(); new CreateCriteria(dicwhere) .Add(ORMRestrictions <System.Int64> .NotEq("PhId", 0)); IList <QtOrgDygxModel> data = QtOrgDygxFacade.Find(dicwhere).Data;//数据库的所有数据 for (int j = 0; j < data.Count; j++) { xmorglist.Add(data[j].Xmorg); } using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { if (".xls".Equals(fileExtension)) { HSSFWorkbook workbook = new HSSFWorkbook(fs); ISheet sheet = workbook.GetSheetAt(0); int rowCount = sheet.LastRowNum; for (int i = 1; i <= rowCount; i++) { QtOrgDygxModel qtOrgDygx = new QtOrgDygxModel(); IRow row = sheet.GetRow(i); ICell cell1 = row.GetCell(0); ICell cell2 = row.GetCell(1); cell1.SetCellType(CellType.String); cell2.SetCellType(CellType.String); string Xmorg = cell1.StringCellValue; string Oldorg = cell2.StringCellValue; if (Xmorg != "" && Oldorg != "") { if (xmorglist.Contains(Xmorg)) { message += Xmorg + "/"; } else { Dictionary <string, object> dicwhere2 = new Dictionary <string, object>(); new CreateCriteria(dicwhere2) .Add(ORMRestrictions <System.String> .Eq("OCode", Xmorg)); OrganizeModel Org = OrganizationFacade.Find(dicwhere2).Data[0]; qtOrgDygx.ParentOrgId = Org.ParentOrgId; qtOrgDygx.IfCorp = Org.IfCorp; qtOrgDygx.Xmorg = Xmorg; qtOrgDygx.Oldorg = Oldorg; qtOrgDygx.PersistentState = PersistentState.Added; qtOrgDygxes.Add(qtOrgDygx); } } } } else if (".xlsx".Equals(fileExtension)) { XSSFWorkbook workbook = new XSSFWorkbook(fs); ISheet sheet = workbook.GetSheetAt(0); int rowCount = sheet.LastRowNum; for (int i = 1; i <= rowCount; i++) { QtOrgDygxModel qtOrgDygx = new QtOrgDygxModel(); IRow row = sheet.GetRow(i); ICell cell1 = row.GetCell(0); ICell cell2 = row.GetCell(1); cell1.SetCellType(CellType.String); cell2.SetCellType(CellType.String); string Xmorg = cell1.StringCellValue; string Oldorg = cell2.StringCellValue; if (Xmorg != "" && Oldorg != "") { if (xmorglist.Contains(Xmorg)) { message += Xmorg + "/"; } else { Dictionary <string, object> dicwhere2 = new Dictionary <string, object>(); new CreateCriteria(dicwhere2) .Add(ORMRestrictions <System.String> .Eq("OCode", Xmorg)); OrganizeModel Org = OrganizationFacade.Find(dicwhere2).Data[0]; qtOrgDygx.ParentOrgId = Org.ParentOrgId; qtOrgDygx.IfCorp = Org.IfCorp; qtOrgDygx.Xmorg = Xmorg; qtOrgDygx.Oldorg = Oldorg; qtOrgDygx.PersistentState = PersistentState.Added; qtOrgDygxes.Add(qtOrgDygx); } } } } } if (message.Length == 0) { result = base.Save <Int64>(qtOrgDygxes, ""); } else { result.Status = ResponseStatus.Error; result.Msg = "导入失败,重复的项目库组织代码:" + message; } return(result); }
/// <summary> /// XLSXs to table data. /// </summary> /// <param name="testCaseId">The test case identifier.</param> /// <param name="sheetName">Name of the sheet.</param> /// <param name="columnNames">The column names.</param> /// <exception cref="System.Exception">You Have Either Specified wrong Sheet name /// + or the specified sheet name does not have data for the specified columns</exception> private void XlsxToTableData(string testCaseId, string sheetName, string[] columnNames) { ArrayList list = new ArrayList(); DataTable dataTable = new DataTable(); int sheetCount = xssfworkbook.NumberOfSheets; Logger.Info(string.Concat("Total Sheets found in the workbook are : [", sheetCount, "]")); ISheet sheet = null; //Get all sheets and based on passed sheet name get the sheet id for (int i = 0; i < sheetCount; i++) { if (xssfworkbook.GetSheetName(i).Equals(sheetName)) { sheet = xssfworkbook.GetSheetAt(i); Logger.Info(string.Concat("User had passed Sheetname: [", sheetName, "]")); Logger.Info(string.Concat("Fetching the data for sheet : [", sheetName + "]")); break; } } //Get the column Header // sheet = xssfworkbook.GetSheetAt(0); Logger.Debug("Fetching the Test Data header information.."); IRow headerRow = sheet.GetRow(0); if (null == headerRow) { string methodName = System.Reflection.MethodBase.GetCurrentMethod().Name; throw new ResourceException(methodName); } IEnumerator rows = sheet.GetRowEnumerator(); //Get the column and row count int columnCount = headerRow.LastCellNum; int rowCount = sheet.LastRowNum; Logger.Info(string.Concat("Total Column count is : [", rowCount + "]")); Logger.Info(string.Concat("Total Row count is : [", columnCount + "]")); //Add the row data table for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) { for (int requiredColumn = 0; requiredColumn < columnNames.Length; requiredColumn++) { if (headerRow.GetCell(columnIndex).ToString().Equals(columnNames[requiredColumn])) { list.Add(columnIndex); dataTable.Columns.Add(headerRow.GetCell(columnIndex).ToString()); } } } //Skip reading the Header data bool skipReadingHeaderRow = rows.MoveNext(); while (rows.MoveNext()) { IRow row = (XSSFRow)rows.Current; DataRow dataRow = dataTable.NewRow(); foreach (int i in list) { ICell cell = row.GetCell(i); if (cell != null && row.GetCell(0).ToString().Equals(testCaseId)) { dataRow[i] = cell.ToString(); } } dataTable.Rows.Add(dataRow); } xssfworkbook = null; sheet = null; testDataSet.Tables.Add(dataTable); }
public void ThemedAndNonThemedColours() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex); XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet; XSSFCellStyle style; XSSFColor color; XSSFCell cell; String[] names = { "White", "Black", "Grey", "Dark Blue", "Blue", "Red", "Green" }; String[] explicitFHexes = { "FFFFFFFF", "FF000000", "FFC0C0C0", "FF002060", "FF0070C0", "FFFF0000", "FF00B050" }; String[] explicitBHexes = { "FFFFFFFF", "FF000000", "FFC0C0C0", "FF002060", "FF0000FF", "FFFF0000", "FF00FF00" }; Assert.AreEqual(7, names.Length); // Check the non-CF colours in Columns A, B, C and E for (int rn = 1; rn < 8; rn++) { int idx = rn - 1; XSSFRow row = sheet.GetRow(rn) as XSSFRow; Assert.IsNotNull(row, "Missing row " + rn); // Theme cells come first XSSFCell themeCell = row.GetCell(0) as XSSFCell; ThemeElement themeElem = ThemeElement.ById(idx); assertCellContents(themeElem.name, themeCell); // Sanity check names assertCellContents(names[idx], row.GetCell(1)); assertCellContents(names[idx], row.GetCell(2)); assertCellContents(names[idx], row.GetCell(4)); // Check the colours // A: Theme Based, Foreground style = themeCell.CellStyle as XSSFCellStyle; color = style.GetFont().GetXSSFColor(); Assert.AreEqual(true, color.IsThemed); Assert.AreEqual(idx, color.Theme); Assert.AreEqual(rgbExpected[idx], HexDump.EncodeHexString(color.RGB)); // B: Theme Based, Foreground cell = row.GetCell(1) as XSSFCell; style = cell.CellStyle as XSSFCellStyle; color = style.GetFont().GetXSSFColor(); Assert.AreEqual(true, color.IsThemed); // TODO Fix the grey theme color in Column B if (idx != 2) { Assert.AreEqual(true, color.IsThemed); Assert.AreEqual(idx, color.Theme); Assert.AreEqual(rgbExpected[idx], HexDump.EncodeHexString(color.RGB)); } else { Assert.AreEqual(1, color.Theme); Assert.AreEqual(0.50, color.Tint, 0.001); } // C: Explicit, Foreground cell = row.GetCell(2) as XSSFCell; style = cell.CellStyle as XSSFCellStyle; color = style.GetFont().GetXSSFColor(); Assert.AreEqual(false, color.IsThemed); Assert.AreEqual(explicitFHexes[idx], color.ARGBHex); // E: Explicit Background, Foreground all Black cell = row.GetCell(4) as XSSFCell; style = cell.CellStyle as XSSFCellStyle; color = style.GetFont().GetXSSFColor(); Assert.AreEqual(true, color.IsThemed); Assert.AreEqual("FF000000", color.ARGBHex); color = style.FillForegroundXSSFColor; Assert.AreEqual(false, color.IsThemed); Assert.AreEqual(explicitBHexes[idx], color.ARGBHex); color = style.FillBackgroundColorColor as XSSFColor; Assert.AreEqual(false, color.IsThemed); Assert.AreEqual(null, color.ARGBHex); } // Check the CF colours // TODO }
protected void Button1_Click(object sender, EventArgs e) { ////1、使用response.TransmitFile传输400MB以上的文件,不使用缓存 //Response.ContentType = "application/x-zip-compressed"; //Response.AddHeader("Content-Disposition", "attachment;filename=text.txt"); //string fileName = Server.MapPath("text.txt"); //Response.TransmitFile(fileName); //Response.Write("<script language=\"javascript\" type=\"text/javascript\">"); //Response.Write("alert(\"下载成功\");"); //Response.Write("window.location.href=\"C_SC.aspx\";"); //Response.Write("</script>"); //2、使用WriteFile方法下载文件 //string filename = Server.MapPath("text.txt"); //Response.ContentEncoding = Encoding.GetEncoding("gb2312"); //Response.WriteFile(filename); //new DataTable DataTable table = new DataTable(); table.Columns.Add(new DataColumn("ID")); table.Columns.Add(new DataColumn("Name")); table.Columns.Add(new DataColumn("time", typeof(DateTime))); table.Rows.Add(1, "name1", DateTime.Now); table.Rows.Add(2, "name2", DateTime.Now); //create excel XSSFWorkbook workbook = new XSSFWorkbook(); workbook.CreateSheet("report"); ISheet reportSheet = workbook.GetSheetAt(0); //set columns value int rowNum = 0; IRow columnRow = reportSheet.CreateRow(rowNum++); for (int i = 0; i < table.Columns.Count; i++) { ICell cell = columnRow.CreateCell(i); cell.SetCellValue(table.Columns[i].ColumnName); } //set cells value foreach (DataRow dataRow in table.Rows) { IRow row = reportSheet.CreateRow(rowNum++); for (int i = 0; i < table.Columns.Count; i++) { ICell cell = row.CreateCell(i); if (dataRow[i] is DateTime) { cell.SetCellValue((DateTime)dataRow[i]); ICellStyle style = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); style.DataFormat = format.GetFormat("yyyy/MM/dd HH:mm:ss"); cell.CellStyle = style; cell.SetCellType(CellType.Numeric); } cell.SetCellValue(dataRow[i].ToString()); } } string filename = "test.xlsx"; workbook.Write(Response.OutputStream); workbook.Close(); Response.ContentType = "application/x-zip-compressed"; Response.AddHeader("Content-Disposition", $"attachment;filename={filename}"); Response.End(); //XSSFWorkbook workbook = new XSSFWorkbook(); //workbook.CreateSheet("sheet1"); //workbook.CreateSheet("sheet2"); //workbook.CreateSheet("sheet3"); //Stream outStream = Response.OutputStream; //workbook.Write(outStream); //outStream.Close(); //workbook.Close(); }
public void UploadInvoice(string Path, Int16?CompanyID, string FileName, string AnchCompanyName) { string ErrorMessage = string.Empty; try { DataTable dt = new DataTable(); string JSONString = string.Empty; var memory = new MemoryStream(); string sFileExtension = FileName.Split('.')[1]; var EmailID = ""; var VendorName = ""; bool sendMail = false; ISheet sheet; using (var stream = new FileStream(Path, FileMode.Open)) { stream.CopyTo(memory); memory.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(memory); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(memory); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } dt.Columns.Add(headerRow.GetCell(j).ToString()); } dt.Columns.Add("Message"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { DataRow dr = dt.NewRow(); IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dr[j] = row.GetCell(j).ToString(); } } dt.Rows.Add(dr); DateTime date; if (dt.Rows[i - 1]["PO Number"].ToString() != "" && dt.Rows[i - 1]["Invoice Date (DD/MM/YYYY)"].ToString() != "" && dt.Rows[i - 1]["Invoice Number"].ToString() != "" && dt.Rows[i - 1]["Invoice Amount"].ToString() != "" && dt.Rows[i - 1]["Payment Due Date (DD/MM/YYYY)"].ToString() != "" && dt.Rows[i - 1]["Approved Amount"].ToString() != "") { if (!DateTime.TryParseExact(dt.Rows[i - 1]["Invoice Date (DD/MM/YYYY)"].ToString(), "dd-MMM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out date)) { dt.Rows[i - 1]["Message"] = "Invoice Date should be in DD/MM/YYYY format"; continue; } if (Convert.ToInt64(dt.Rows[i - 1]["Invoice Amount"].ToString()) < Convert.ToInt64(dt.Rows[i - 1]["Approved Amount"].ToString())) { dt.Rows[i - 1]["Message"] = "Approved amount should be less than invoice amount"; continue; } if (!DateTime.TryParseExact(dt.Rows[i - 1]["Payment Due Date (DD/MM/YYYY)"].ToString(), "dd-MMM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal, out date)) { dt.Rows[i - 1]["Message"] = "Payment Due Date should be in DD/MM/YYYY format"; continue; } else { dt.Rows[i - 1]["Message"] = "Success"; } } else { if (dt.Rows[i - 1]["PO Number"].ToString() == "") { dt.Rows[i - 1]["Message"] = "PO Number should not be blank"; continue; } if (dt.Rows[i - 1]["Invoice Date (DD/MM/YYYY)"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Invoice Date should not be blank"; continue; } if (dt.Rows[i - 1]["Invoice Number"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Invoice Number should not be blank"; continue; } if (dt.Rows[i - 1]["Invoice Amount"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Invoice Amount should not be blank"; continue; } if (dt.Rows[i - 1]["Payment Due Date (DD/MM/YYYY)"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Payment Due Date should not be blank"; continue; } if (dt.Rows[i - 1]["Approved Amount"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Approved Amount should not be blank"; continue; } } Company objDatawithSP = CheckEmailID(row.Cells[6].ToString()); if (objDatawithSP != null) { EmailID = objDatawithSP.Contact_email; VendorName = objDatawithSP.Company_name; } else { dt.Rows[i - 1]["Message"] = "Pan Number is not valid"; continue; } var Result = InsertInvoiceRecord(row, CompanyID); if (Convert.ToInt32(Result.Value) == -1) { dt.Rows[i - 1]["Message"] = "Invoice Number already Exists"; continue; } sendMail = true; } //sending email if (sendMail) { string Template = GetInvoiceRegisterMailTemplate(); string path = Template; //string tag = lstAwaitedInvVendorsView.ElementAt(0).TableTag; string EMAIL_TOKEN_PAYMENT_LINK = "##$$PAYMENT_LINK$$##"; string paymentLink = "http://dotnet.brainvire.com/Finocart/Account/AdminLogin";///change url string MailStatus = string.Empty; string subject = "Vendor registration"; WebClient client = new WebClient(); string startupPath = Environment.CurrentDirectory; string body = path; StringBuilder sb = new StringBuilder(); //IEnumerable<GetUploadVendorListModel1> lstAwaitedInvVendorsView1 = _companyRepository.GetUploadVendorList1(VendorID); //for (int i = 1; i < 5; i++) //{ body = body.Replace("@@VendorName@@", VendorName); body = body.Replace("@@AnchorName@@", AnchCompanyName); body = body.Replace(EMAIL_TOKEN_PAYMENT_LINK, paymentLink); //body = body.Replace("@@PODate(MM/DD/YYYY)@@", "Abc"); //body = body.Replace("@@InvoiceNumber@@", "Abc"); //body = body.Replace("@@InvoiceAmount@@", "Abc"); //body = body.Replace("@@PaymentDueDate(MM/DD/YYYY)@@", "Abc"); //body = body.Replace("@@ApprovedAmount@@", "Abc"); //body = body.Replace("@@PaymentDays@@", "Abc"); IEnumerable <LookupDetail> lookupDetails = getLookupDetailByKey("SMTPInfo"); SendEmail(lookupDetails, EmailID, subject, body, true); } } JSONString = JsonConvert.SerializeObject(dt); GetLog(JSONString, "Invoice", CompanyID, AnchCompanyName, FileName); //HttpContext.Session.SetString("Excel", JSONString); //if (file.FileName == "InvoiceTemplate.xlsx") //{ // file.FileName.Remove(1); //} //return Json(new { result = dt }); } catch (Exception ex) { var st = new StackTrace(ex, true); var frame = st.GetFrame(0); int ErrorLine = frame.GetFileLineNumber(); //var Result = _CommonRepository.LogManagement(ControllerName, ActionName, ex.Message, ErrorLine, UserID); throw ex; } }
//private readonly IHostingEnvironment _hostingEnvironment; //public UploadExcelDetails() //{ //} //public UploadExcelDetails(IHostingEnvironment hostingEnvironment) //{ // _hostingEnvironment = hostingEnvironment; //} public void UploadVendors(string Path, Int64?CompanyID, string FileName, string CompanyName) { try { DataTable dt = new DataTable(); string JSONString = string.Empty; var memory = new MemoryStream(); string sFileExtension = FileName.Split('.')[1]; // var FileName = CompanyName + DateTime.Now.ToString("yyyyMMddhhmmss"); ISheet sheet; //string fullPath = Path.Combine(FileName + sFileExtension); using (var stream = new FileStream(Path, FileMode.Open)) { stream.CopyTo(memory); memory.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(memory); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(memory); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } dt.Columns.Add(headerRow.GetCell(j).ToString()); } dt.Columns.Add("Message"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { DataRow dr = dt.NewRow(); IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { dr[j] = row.GetCell(j).ToString(); } } dt.Rows.Add(dr); if (dt.Rows[i - 1]["Vendor Name"].ToString() != "" && dt.Rows[i - 1]["Pan Number"].ToString() != "" && dt.Rows[i - 1]["Contact Person Name"].ToString() != "" && dt.Rows[i - 1]["Email ID"].ToString() != "" && dt.Rows[i - 1]["Contact Number"].ToString() != "") { if (!Regex.IsMatch(dt.Rows[i - 1]["Pan Number"].ToString(), @"^[a-zA-Z]{5}[0-9]{4}[a-zA-Z]{1}$")) { dt.Rows[i - 1]["Message"] = "Pan Number is not valid"; continue; } if (dt.Rows[i - 1]["MSME (Yes/No)"].ToString().ToLower() != "") { if (dt.Rows[i - 1]["MSME (Yes/No)"].ToString().ToLower() == "yes") { if (dt.Rows[i - 1]["UAM Number"].ToString() == "") { dt.Rows[i - 1]["Message"] = "UAM Number should not be blank"; continue; } } } if (dt.Rows[i - 1]["UAM Number"].ToString() != "") { if (dt.Rows[i - 1]["MSME (Yes/No)"].ToString().ToLower() == "") { dt.Rows[i - 1]["Message"] = "MSME should not be blank"; continue; } } if (!Regex.IsMatch(dt.Rows[i - 1]["Email ID"].ToString(), @"^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$")) { dt.Rows[i - 1]["Message"] = "Email ID is not valid"; continue; } else { dt.Rows[i - 1]["Message"] = "Success"; } } else { if (dt.Rows[i - 1]["Vendor Name"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Vendor Name should not be blank"; continue; } if (dt.Rows[i - 1]["Pan Number"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Pan Number should not be blank"; continue; } if (dt.Rows[i - 1]["Contact Person Name"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Contact Person Name should not be blank"; continue; } if (dt.Rows[i - 1]["Email ID"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Email ID should not be blank"; continue; } if (dt.Rows[i - 1]["Contact Number"].ToString() == "") { dt.Rows[i - 1]["Message"] = "Contact Number should not be blank"; continue; } } string randomPassword = GeneratePassword(); string Password = SecurityHelperService.Encrypt(randomPassword); var Result = InsertVendorRecord(dr, CompanyID, Password); if (Convert.ToInt32(Result.Value) > 0) { //string Template = string.Empty; string Template = GetVendorRegisterMailTemplate(); string path = Template; string EMAIL_TOKEN_PAYMENT_LINK = "##$$PAYMENT_LINK$$##"; string paymentLink = "http://dotnet.brainvire.com/Finocart/Account/AdminLogin";///change url //string MailStatus = string.Empty; string emailToAddress = dr[6].ToString(); string subject = "Vendor registration"; WebClient client = new WebClient(); string startupPath = Environment.CurrentDirectory; string body = path; // string body = client.DownloadString(startupPath + "/Views/Template/EmailTemplate.cshtml"); body = body.Replace("@@User@@", dr[0].ToString()); body = body.Replace("@@PanNumber@@", dr[1].ToString()); body = body.Replace("@@ProjectName@@", "Finocart"); body = body.Replace("@@VendorName@@", dt.Rows[i - 1]["Vendor Name"].ToString()); body = body.Replace("@@AnchorCompanyname@@", CompanyName); body = body.Replace(EMAIL_TOKEN_PAYMENT_LINK, paymentLink); body = body.Replace("@@PanNumber@@", dt.Rows[i - 1]["Pan Number"].ToString()); body = body.Replace("@@Password@@", randomPassword); IEnumerable <LookupDetail> lookupDetails = getLookupDetailByKey("SMTPInfo"); SendEmail(lookupDetails, emailToAddress, subject, body, true); } if (Convert.ToInt32(Result.Value) == -1) { dt.Rows[i - 1]["Message"] = "Pan Number already exists"; continue; } } //GetLog(dt); } //} JSONString = JsonConvert.SerializeObject(dt); GetLog(JSONString, "Vendor", CompanyID, CompanyName, FileName); //HttpContext.Session.SetString("Excel", JSONString); //return Json(new { result = dt }); } catch (Exception ex) { throw ex; } }
private void ExportIDL(string fileName, string exportDir) { XSSFWorkbook xssfWorkbook; using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { xssfWorkbook = new XSSFWorkbook(file); } string protoName = Path.GetFileNameWithoutExtension(fileName).ToLower(); string exportPath = Path.Combine(exportDir, $"{protoName}.fbs"); using (FileStream txt = new FileStream(exportPath, FileMode.Create)) using (StreamWriter sw = new StreamWriter(txt)) { StringBuilder sb = new StringBuilder(); ISheet sheet = xssfWorkbook.GetSheetAt(0); sb.Append("namespace fb; \n"); //gen TB sb.Append($"table {protoName}TB\n"); sb.Append("{\n"); sb.Append($"\t {protoName}TRS:[{protoName}TR];\n"); sb.Append("}\n\n"); //end TB //gen TR sb.Append($"table {protoName}TR\n"); sb.Append("{\n"); int cellCount = sheet.GetRow(0).LastCellNum; for (int i = 0; i < cellCount; i++) { string fieldDesc = ExcelHelper.GetCellString(sheet, 0, i); if (fieldDesc.StartsWith("#")) { continue; } string fieldName = ExcelHelper.GetCellString(sheet, 1, i).ToLower(); string fieldType = ExcelHelper.GetCellString(sheet, 2, i); if (fieldType == "" || fieldName == "") { continue; } string idlType = Convert(fieldType); //key if (fieldName.Equals("_id")) { idlType += "(key)"; } sb.Append($"\t {fieldName}:{idlType};\n"); } sb.Append("}\n"); //end TR sb.Append($"root_type {protoName}TB;"); sw.Write(sb.ToString()); } }
public bool insertData(string fileLocation, string importType) { try { string conn = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString; // 建立一個工作簿 XSSFWorkbook excel; // 檔案讀取 using (FileStream files = new FileStream(fileLocation, FileMode.Open, FileAccess.Read)) { excel = new XSSFWorkbook(files); // 將剛剛的Excel 讀取進入到工作簿中 } // Excel 的哪一個活頁簿 ISheet sheet = excel.GetSheetAt(0); using (SqlConnection sqlconnection = new SqlConnection(conn)) { sqlconnection.Open(); string sqlcommandstring = ""; SqlCommand sqlcommand; for (int row = 1; row <= sheet.LastRowNum; row++) // 使用For 走訪所有的資料列 { //--------------insert value-------- string Con_ID = ""; string Con_ChiNAME = ""; string Con_EngNAME = ""; string Parent_Con_ID = ""; string Role = "CON"; bool IsTeach = false; string Cli_ID = ""; int InvestType = 2; DateTime InvestDate; string new_InvestDate = ""; double Amount = 0; //---------------------------------- if (sheet.GetRow(row) != null) // 驗證是不是空白列 { //for (int c = 0; c <= sheet.GetRow(row).LastCellNum; c++) // 使用For 走訪資料欄 //{ //} if (importType == "0") { if (sheet.GetRow(row).GetCell(0) != null) { Con_ID = sheet.GetRow(row).GetCell(0).StringCellValue; // 字串 } if (Con_ID == "") //空白行跳過 { continue; } if (sheet.GetRow(row).GetCell(1) != null) { Con_ChiNAME = sheet.GetRow(row).GetCell(1).StringCellValue; } if (sheet.GetRow(row).GetCell(2) != null) { Con_EngNAME = sheet.GetRow(row).GetCell(2).StringCellValue; } if (sheet.GetRow(row).GetCell(3) != null) { Parent_Con_ID = sheet.GetRow(row).GetCell(3).StringCellValue; } //double Con_ChiNAME = sheet.GetRow(row).GetCell(1).NumericCellValue; // 布林 if (Parent_Con_ID == "股東") { Parent_Con_ID = "000"; Role = "SHA"; } sqlcommandstring = @" delete ConInfo where Con_ID=N'" + Con_ID + @"'; delete ConInfoDetail where Con_ID=N'" + Con_ID + @"'; insert into ConInfoDetail(Con_ID,Con_ChiNAME_Last,Con_ChiNAME_First) values(N'" + Con_ID + @"',N'" + Con_ChiNAME + @"',' '); insert into ConInfo(Con_ID,Parent_Con_ID,Con_ROLE,Con_PATH,IsAuto,CREATE_DATE,UPDATE_DATE) select N'" + Con_ID + "',N'" + Parent_Con_ID + "','" + Role + "',Con_PATH+'/" + Con_ID + @"',1,GETDATE(),GETDATE() from ConInfo where Con_ID=N'" + Parent_Con_ID + "' "; sqlcommand = new SqlCommand(sqlcommandstring, sqlconnection); sqlcommand.ExecuteNonQuery(); } else if (importType == "1") { if (sheet.GetRow(row).GetCell(0) != null) { Cli_ID = sheet.GetRow(row).GetCell(0).StringCellValue; // 字串 } if (Cli_ID == "") //空白行跳過 { continue; } if (sheet.GetRow(row).GetCell(1) != null) { Con_ChiNAME = sheet.GetRow(row).GetCell(1).StringCellValue; } if (sheet.GetRow(row).GetCell(2) != null) { Con_EngNAME = sheet.GetRow(row).GetCell(2).StringCellValue; } if (sheet.GetRow(row).GetCell(3) != null) { Parent_Con_ID = sheet.GetRow(row).GetCell(3).StringCellValue; } if (sheet.GetRow(row).GetCell(4) != null) { InvestType = (int)sheet.GetRow(row).GetCell(4).NumericCellValue; } if (sheet.GetRow(row).GetCell(5) != null) { InvestDate = sheet.GetRow(row).GetCell(5).DateCellValue; new_InvestDate = InvestDate.ToString("yyyy/MM/dd"); if (new_InvestDate == "0001/01/01") { new_InvestDate = null; } } else { InvestDate = DateTime.Now; new_InvestDate = null; } if (sheet.GetRow(row).GetCell(6) != null) { Amount = sheet.GetRow(row).GetCell(6).NumericCellValue; } sqlcommandstring = @"SELECT ISNULL(max(Deposit_ID),0) as Deposit_ID FROM DepositList"; sqlcommand = new SqlCommand(sqlcommandstring, sqlconnection); string Deposit_ID = Convert.ToString(Convert.ToInt32(Convert.ToString(sqlcommand.ExecuteScalar()).Substring(1)) + 1); while (Deposit_ID.Length < 9) { Deposit_ID = "0" + Deposit_ID; } Deposit_ID = "D" + Deposit_ID; sqlcommandstring = @" delete CliInfo where Cli_ID='" + Cli_ID + @"'; delete CliInfoDetail where Cli_ID='" + Cli_ID + @"'; insert into CliInfo (Cli_ID,Con_ID,CREATE_DATE,UPDATE_DATE) values('" + Cli_ID + "','" + Parent_Con_ID + @"',GETDATE(),GETDATE()) ; insert into CliInfoDetail (Cli_ID,Con_ID,Cli_ChiNAME_Last)values('" + Cli_ID + "','" + Parent_Con_ID + @"',N'" + Con_ChiNAME + @"') insert into DepositList(Deposit_ID,Cli_ID,Con_ID,Deposit_Amount,Deposit_Type,Deposit_DATE,Arrival_DATE,Status,CREATE_DATE) values('" + Deposit_ID + "','" + Cli_ID + "','" + Parent_Con_ID + "','" + Amount + "','" + InvestType + "','" + new_InvestDate + "','" + new_InvestDate + "','2',GETDATE())"; sqlcommand = new SqlCommand(sqlcommandstring, sqlconnection); sqlcommand.ExecuteNonQuery(); } } } } return(true); } catch (Exception ex) { return(false); } }
private List <Player> LoadFromFile(string path) { var fileStream = File.OpenRead(path); var workbook = new XSSFWorkbook(fileStream); List <Player> players = new List <Player>(); var sheetIndex = 0; while (sheetIndex < workbook.NumberOfSheets) { var sheet = workbook.GetSheetAt(sheetIndex++); var player = new Player() { Name = sheet.SheetName }; var rowIndex = 0; while (rowIndex <= sheet.LastRowNum) { var row = sheet.GetRow(rowIndex++); var placeName = row.GetCell(0).StringCellValue; Place.PlaceType placeType = Place.PlaceType.Top; switch (placeName) { case "上路": placeType = Place.PlaceType.Top; break; case "打野": placeType = Place.PlaceType.Jungle; break; case "中路": placeType = Place.PlaceType.Middle; break; case "下路": placeType = Place.PlaceType.Bottom; break; case "辅助": placeType = Place.PlaceType.Support; break; } var colIndex = 1; while (colIndex < row.LastCellNum) { var heroName = row.GetCell(colIndex++).StringCellValue; if (string.IsNullOrEmpty(heroName)) { continue; } Place place = new Place() { Type = placeType, Hero = HeroConfig.GetHero(heroName) }; player.Places.Add(place); } } players.Add(player); } return(players); }
protected void Page_Load(object sender, EventArgs e) { //讀取Token值 string token = (string.IsNullOrEmpty(Request["InfoToken"])) ? "" : Request["InfoToken"].ToString().Trim(); if (VeriftyToken(token)) { //建立共用connection & transaction SqlConnection oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); oConn.Open(); SqlCommand oCmd = new SqlCommand(); oCmd.Connection = oConn; SqlTransaction myTrans = oConn.BeginTransaction(); oCmd.Transaction = myTrans; //建立DataTable Bulk Copy用 DataTable dt = new DataTable(); dt.Columns.Add("Re_CityNo", typeof(string)).MaxLength = 2;; dt.Columns.Add("Re_CityName", typeof(string)).MaxLength = 10; dt.Columns.Add("Re_StreetStandYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_StreetStand", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_Re_StreetVendorYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_StreetVendor", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_StreetVendorIncomeYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_StreetVendorIncome", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_StreetVendorAvgIncomeYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_StreetVendorAvgIncome", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_RetailBusinessSalesYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_RetailBusinessSales", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_RetailBusinessSalesRateYearDesc", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_RetailBusinessSalesRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Re_RetailBusinessAvgSalesYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_RetailBusinessAvgSales", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_CreateDate", typeof(DateTime)); dt.Columns.Add("Re_CreateID", typeof(string)); dt.Columns.Add("Re_CreateName", typeof(string)); dt.Columns.Add("Re_Status", typeof(string)); dt.Columns.Add("Re_Version", typeof(int)); try { HttpFileCollection uploadFiles = Request.Files;//檔案集合 HttpPostedFile aFile = uploadFiles[0]; //判斷有沒有檔案 if (uploadFiles.Count < 1 || aFile.FileName == "") { throw new Exception("請選擇檔案"); } //有檔案繼續往下做 if (uploadFiles.Count > 0) { string extension = (System.IO.Path.GetExtension(aFile.FileName) == "") ? "" : System.IO.Path.GetExtension(aFile.FileName); if (extension != ".xls" && extension != ".xlsx") { throw new Exception("請選擇xls或xlsx檔案上傳"); } IWorkbook workbook;// = new HSSFWorkbook();//创建Workbook对象 workbook = new XSSFWorkbook(aFile.InputStream); ISheet sheet = workbook.GetSheetAt(0);//當前sheet //簡易判斷這份Excel是不是零售的Excel int cellsCount = sheet.GetRow(0).Cells.Count; //1.判斷表頭欄位數 if (cellsCount != 8) { throw new Exception("請檢查是否為零售的匯入檔案"); } //2.檢查欄位名稱 if (sheet.GetRow(0).GetCell(1).ToString().Trim() != "攤販經營家數" || sheet.GetRow(0).GetCell(2).ToString().Trim() != "攤販從業人數") { throw new Exception("請檢查是否為零售的匯入檔案"); } //取得當前最大版次 (+1變成現在版次) strMaxVersion = RL_DB.getMaxVersin() + 1; //取得代碼檔 CodeTable_DB code_db = new CodeTable_DB(); DataTable dtCode = code_db.getCommonCode("02"); string cityNo = string.Empty; //資料從第四筆開始 最後一筆是合計不進資料庫 for (int j = 3; j < sheet.PhysicalNumberOfRows - 1; j++) { if (sheet.GetRow(j).GetCell(0).ToString().Trim() != "" && sheet.GetRow(j).GetCell(0).ToString().Trim() != "全台平均") { DataRow row = dt.NewRow(); cityNo = Common.GetCityCodeItem(dtCode, sheet.GetRow(j).GetCell(0).ToString().Trim());//縣市代碼 if (cityNo == "") { throw new Exception("第" + (j + 1) + "筆資料:" + sheet.GetRow(j).GetCell(0).ToString().Trim() + "不是一個正確的縣市名稱"); } strErrorMsg = "行數:第" + (j + 1).ToString() + " 筆<br>"; row["Re_CityNo"] = cityNo; //縣市代碼 row["Re_CityName"] = sheet.GetRow(j).GetCell(0).ToString().Trim(); //縣市名稱 row["Re_StreetStandYear"] = sheet.GetRow(1).GetCell(1).ToString().Trim().Replace("年", ""); //攤販經營家數-資料年度(民國年) row["Re_StreetStand"] = sheet.GetRow(j).GetCell(1).ToString().Trim(); //攤販經營家數-家 row["Re_Re_StreetVendorYear"] = sheet.GetRow(1).GetCell(2).ToString().Trim().Replace("年", ""); //攤販從業人數-資料年度(民國年) row["Re_StreetVendor"] = sheet.GetRow(j).GetCell(2).ToString().Trim(); //攤販從業人數-人 row["Re_StreetVendorIncomeYear"] = sheet.GetRow(1).GetCell(3).ToString().Trim().Replace("年", ""); //攤販全年收入-資料年度(民國年) row["Re_StreetVendorIncome"] = sheet.GetRow(j).GetCell(3).ToString().Trim(); //攤販全年收入-千元 row["Re_StreetVendorAvgIncomeYear"] = sheet.GetRow(1).GetCell(4).ToString().Trim().Replace("年", ""); //攤販全年平均收入-資料年度(民國年) row["Re_StreetVendorAvgIncome"] = sheet.GetRow(j).GetCell(4).ToString().Trim(); //攤販全年平均收入-千元 row["Re_RetailBusinessSalesYear"] = sheet.GetRow(1).GetCell(5).ToString().Trim().Replace("年", ""); //零售業營利事業銷售額-資料年度(民國年) row["Re_RetailBusinessSales"] = sheet.GetRow(j).GetCell(5).ToString().Trim(); //零售業營利事業銷售額-千元 row["Re_RetailBusinessSalesRateYearDesc"] = sheet.GetRow(1).GetCell(6).ToString().Trim(); //零售業營利事業銷售額成長率-年度敘述 EX: 106-107年 row["Re_RetailBusinessSalesRate"] = sheet.GetRow(j).GetCell(6).ToString().Trim(); //零售業營利事業銷售額成長率 row["Re_RetailBusinessAvgSalesYear"] = sheet.GetRow(1).GetCell(7).ToString().Trim().Replace("年", ""); //零售業營利事業平均每家銷售額-資料年度(民國年) row["Re_RetailBusinessAvgSales"] = sheet.GetRow(j).GetCell(7).ToString().Trim(); //零售業營利事業平均每家銷售額-千元 row["Re_CreateDate"] = dtNow; row["Re_CreateID"] = LogInfo.mGuid; //上傳者GUID row["Re_CreateName"] = LogInfo.name; //上傳者姓名 row["Re_Status"] = "A"; row["Re_Version"] = strMaxVersion; dt.Rows.Add(row); } } if (dt.Rows.Count > 0) { strErrorMsg = ""; BeforeBulkCopy(oConn, myTrans); //檢查資料表裡面是不是有該年的資料 DoBulkCopy(myTrans, dt, strErrorMsg); //匯入 myTrans.Commit(); //最後再commit } } } catch (Exception ex) { strErrorMsg += "錯誤訊息:" + ex.Message + "<br>"; strErrorMsg += "(欄位名稱請參考上傳範例檔)"; myTrans.Rollback(); } finally { oCmd.Connection.Close(); oConn.Close(); if (strErrorMsg == "") { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:零售 , 狀態:上傳成功"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('零售匯入成功');</script>"); } else { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:零售 , 狀態:上傳失敗"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } } } else { strErrorMsg = "連線失敗請重新登入"; Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } }
public static void ExportToExcel(MatchResultViewModel vm) { #region initialize workbook and set font colors string templatePath = @"template.xlsx"; string filePath = $"{DateTime.Now:yyyyMMdd-HHmm}.xlsx"; var stream = File.OpenRead(templatePath); IWorkbook workbook = new XSSFWorkbook(stream); ISheet sheet = workbook.GetSheetAt(0); stream.Close(); //var whiteFont = workbook.CreateFont(); //whiteFont.FontName = "Segoe UI Symbol"; //whiteFont.FontHeightInPoints = 11; //whiteFont.Color = 0; //var redFont = workbook.CreateFont(); //redFont.FontName = "Segoe UI Symbol"; //redFont.FontHeightInPoints = 11; //redFont.Color = 2; //var greenFont = workbook.CreateFont(); //greenFont.FontName = "Segoe UI Symbol"; //greenFont.FontHeightInPoints = 11; //greenFont.Color = 3; //var purpleFont = workbook.CreateFont(); //purpleFont.FontName = "Titillium Web SemiBold"; //purpleFont.FontHeightInPoints = 11; //purpleFont.Color = 20; #endregion for (int i = 0; i < 22; i++) { var player = vm.PlayerResults[i]; if (player.FinishPosition == 0) { continue; } // driver does not exist #region write data to workbook IRow row = sheet.GetRow(player.FinishPosition); if (player.Name.Length > 0) { row.GetCell(1).SetCellValue(player.Name); } row.GetCell(2).SetCellValue(player.QualiTimeString); row.GetCell(3).SetCellValue(player.GridPosition); int positionGained = player.GridPosition - player.FinishPosition; var cellGrid = row.GetCell(4); var cellPosGained = row.GetCell(5); if (positionGained > 0) { cellGrid.SetCellValue("▲"); cellPosGained.SetCellValue(positionGained); } else if (positionGained < 0) { cellGrid.SetCellValue("▼"); cellPosGained.SetCellValue(-positionGained); } else { cellPosGained.SetCellValue(0); } row.GetCell(6).SetCellValue(player.TyreStintsString); row.GetCell(7).SetCellValue(player.FastestLapString); //if (item.BestLapTimeSeconds == fastestLapOfTheRace) //{ // row.GetCell(7).CellStyle.SetFont(purpleFont); //} row.GetCell(8).SetCellValue(player.FinishTimeString); row.GetCell(9).SetCellValue(player.PenaltyString); row.GetCell(10).SetCellValue(player.Point); #endregion } #region export to Excel file var outputStream = File.Create(filePath); workbook.Write(outputStream); outputStream.Close(); #endregion }
public IActionResult ExportToExcel() { //创建EXCEL工作薄 IWorkbook workBook = new XSSFWorkbook(); //创建sheet文件表 ISheet sheet = workBook.CreateSheet("客户信息"); var expDir = string.Format("{0}Export\\{1}", System.AppDomain.CurrentDomain.BaseDirectory, DateTime.Now.ToString("yyyyMM")); if (!Directory.Exists(expDir)) { Directory.CreateDirectory(expDir); } string filePath = string.Format("{0}\\CD{1}.xlsx", expDir, DateTime.Now.ToString("yyyyMMddHHmmss")); #region 创建Excel表头 //创建表头 IRow header = sheet.CreateRow(0); ICell cell = header.CreateCell(0); cell.SetCellValue("客户编号"); cell = header.CreateCell(1); cell.SetCellValue("客户名称"); cell = header.CreateCell(2); cell.SetCellValue("客户类型"); cell = header.CreateCell(3); cell.SetCellValue("联系电话/Phone"); cell = header.CreateCell(4); cell.SetCellValue("传真/Fax"); cell = header.CreateCell(5); cell.SetCellValue("邮箱/Email"); //IName range = workBook.CreateName();//创建一个命名公式 //range.RefersToFormula = "客户信息!$A$1:$A$4";//公式内容,就是上面的区域 //range.NameName = "sectionName";//公式名称,可以在"公式"-->"名称管理器"中看到 ISheet sheet1 = workBook.GetSheetAt(0); //获得第一个工作表 CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 2, 2); //设定位置 行起,行止,列起,列终 XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[] { "Company", "Personal", "Virtual", "Internal" }), regions); //创建一个特定约束范围内的公式列表约束(即第一节里说的"自定义"方式) validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示 validation.ShowErrorBox = true; //显示上面提示 = True sheet1.AddValidationData(validation); //添加进去 sheet1.ForceFormulaRecalculation = true; #endregion //工作流写入,通过流的方式进行创建生成文件 using (MemoryStream stream = new MemoryStream()) { workBook.Write(stream); byte[] buffer = stream.ToArray(); return(File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("客户信息表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss")))); } }
public async Task <ActionResult> OnPostImporttest([FromForm] test _test) { var value = HttpContext.Session.GetString(SessionToken); if (string.IsNullOrEmpty(value)) { string token = await getToken(); HttpContext.Session.SetString(SessionToken, token); value = HttpContext.Session.GetString(SessionToken); } IFormFile file = Request.Form.Files[0]; string folderName = "Upload"; string webRootPath = _hostingEnvironment.WebRootPath; string newPath = Path.Combine(webRootPath, folderName); StringBuilder sb = new StringBuilder(); if (!Directory.Exists(newPath)) { Directory.CreateDirectory(newPath); } if (file.Length > 0) { string sFileExtension = Path.GetExtension(file.FileName).ToLower(); ISheet sheet; string fullPath = Path.Combine(newPath, file.FileName); using (var stream = new FileStream(fullPath, FileMode.Create)) { file.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row int cellCount = headerRow.LastCellNum; sb.Append("<table class='table'><tr>"); for (int j = 0; j < cellCount; j++) { NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) { continue; } sb.Append("<th>" + cell.ToString() + "</th>"); } sb.Append("</tr>"); sb.AppendLine("<tr>"); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { User _in = new User(); IRow row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } _in.Team = new Team(); _in.Contact = new List <Contact>(); _in.UserName = row.GetCell(4).ToString(); _in.Password = "******"; _in.LookUpUserTypeId = 4; _in.LookUpUserStatusId = 1; _in.RecordStatus = "Active"; Team _inTeam = new Team(); _inTeam.TeamName = row.GetCell(1).ToString(); _inTeam.TeamDescription = null; _inTeam.TeamLogo = null; _in.Team = _inTeam; List <Contact> _inConL = new List <Contact>(); Contact _inCon = new Contact(); _inCon.LookUpContactTypeId = 1; _inCon.Value = row.GetCell(4).ToString(); _inConL.Add(_inCon); Contact _inConP = new Contact(); _inConP.LookUpContactTypeId = 2; _inConP.Value = row.GetCell(5).ToString(); _inConL.Add(_inConP); _in.Contact = _inConL; User _out = await _login.AddUserDetails(value, _in); Citizen _citizen = new Citizen(); _citizen.UserId = (int)_out.UserId; _citizen.CaptainName = row.GetCell(0).ToString(); _citizen.PlayerName = row.GetCell(1).ToString(); _citizen.CompanyName = row.GetCell(2).ToString(); _citizen.Designation = row.GetCell(3).ToString(); _citizen.DOB = Convert.ToDateTime(row.GetCell(6).ToString()); _citizen.BloodGroup = row.GetCell(7).ToString(); _citizen.HREmail = row.GetCell(8).ToString(); _citizen.HRPhone = row.GetCell(9).ToString(); _citizen.CompanyEmail = row.GetCell(4).ToString(); bool _outData = await _user.PostCitizenDetails(value, _citizen); //sb.Append("<td>" + row.GetCell(j).ToString() + "</td>"); sb.AppendLine("</tr>"); } sb.Append("</table>"); } } return(this.Content(sb.ToString())); }
/// <summary> /// 利用NPOI导入Excel文件数据 /// </summary> /// <param name="XlsFilesPathName">获取选定文件的路径名</param> /// <returns></returns> public static DataTable NPOIImportExcelFile(string XlsFilesPathName) { try { DataTable ExcelTable = new DataTable();//创建填充数据表 if (XlsFilesPathName == string.Empty) { MessageBoxEx.Show("请您选择要导入的配件信息文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } else if (Path.GetExtension(XlsFilesPathName) != ".xlsx" && Path.GetExtension(XlsFilesPathName) != ".xls") { MessageBoxEx.Show("请您选择Excel格式的文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); } else { IWorkbook WkBk = null; //创建Excel工作簿 ISheet sht = null; //获取第一个sheet表 IRow HeaderRow = null; //获取Excel标题行 FileStream fsxls = new FileStream(XlsFilesPathName, FileMode.Open, FileAccess.Read); //读取Excel文件流 if (Path.GetExtension(XlsFilesPathName) == ".xlsx") { WkBk = new XSSFWorkbook(fsxls); //生成支持Office2007以上版本的Excel表格工作簿 sht = (XSSFSheet)WkBk.GetSheetAt(0); //获取第一个sheet表数据 HeaderRow = (XSSFRow)sht.GetRow(0); //获取Excel标题行 } else if (Path.GetExtension(XlsFilesPathName) == ".xls") { WkBk = new HSSFWorkbook(fsxls); //生成支持Office2003及以下版本的Excel表格工作簿 sht = (HSSFSheet)WkBk.GetSheetAt(0); //获取第一个sheet表数据 HeaderRow = (HSSFRow)sht.GetRow(0); //获取Excel标题行 } fsxls.Close(); fsxls.Dispose(); int FirstCellIndx = HeaderRow.FirstCellNum; //获取Excel标题行首列索引 int LastCellIndx = HeaderRow.LastCellNum; //获取Excel标题行尾列索引 //创建表列 for (int i = FirstCellIndx; i < LastCellIndx; i++) { DataColumn DataCol = new DataColumn(); DataCol.ColumnName = HeaderRow.GetCell(i).StringCellValue; //标题列名称 ExcelTable.Columns.Add(DataCol); //添加表列名 } int FirstRowIndx = sht.FirstRowNum; //获取Excel首行索引 int LastRowIndx = sht.LastRowNum; //获取Excel首行索引 //创建表行 for (int j = FirstRowIndx + 1; j < LastRowIndx; j++) { IRow DatasRow = sht.GetRow(j); DataRow TableRow = ExcelTable.NewRow();//创建表行 for (int k = FirstCellIndx; k < LastCellIndx; k++) { //获取Excel表行数据值 string XlsCellValue = DatasRow.GetCell(k).ToString() == string.Empty ? "" : DatasRow.GetCell(k).ToString(); TableRow[k] = XlsCellValue; } ExcelTable.Rows.Add(TableRow);//添加表行 } WkBk = null; sht = null; } return(ExcelTable);//返回数据表 } catch (Exception ex) { MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question); return(null); } }
/// <summary> /// Excel导入 /// </summary> /// <param name="filePath"></param> /// <returns></returns> public List <T> ImportFromExcel(string filePath) { string absoluteFilePath = GlobalContext.HostingEnvironment.ContentRootPath + filePath.Replace(Path.AltDirectorySeparatorChar, Path.DirectorySeparatorChar); List <T> list = new List <T>(); HSSFWorkbook hssfWorkbook = null; XSSFWorkbook xssWorkbook = null; ISheet sheet = null; using (FileStream file = new FileStream(absoluteFilePath, FileMode.Open, FileAccess.Read)) { switch (Path.GetExtension(filePath)) { case ".xls": hssfWorkbook = new HSSFWorkbook(file); sheet = hssfWorkbook.GetSheetAt(0); break; case ".xlsx": xssWorkbook = new XSSFWorkbook(file); sheet = xssWorkbook.GetSheetAt(0); break; default: throw new Exception("不支持的文件格式"); } } IRow columnRow = sheet.GetRow(1); // 第二行为字段名 Dictionary <int, PropertyInfo> mapPropertyInfoDict = new Dictionary <int, PropertyInfo>(); for (int j = 0; j < columnRow.LastCellNum; j++) { ICell cell = columnRow.GetCell(j); PropertyInfo propertyInfo = MapPropertyInfo(cell.ParseToString()); if (propertyInfo != null) { mapPropertyInfoDict.Add(j, propertyInfo); } } for (int i = (sheet.FirstRowNum + 2); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); T entity = new T(); for (int j = row.FirstCellNum; j < columnRow.LastCellNum; j++) { if (mapPropertyInfoDict.ContainsKey(j)) { if (row.GetCell(j) != null) { PropertyInfo propertyInfo = mapPropertyInfoDict[j]; switch (propertyInfo.PropertyType.ToString()) { case "System.DateTime": case "System.Nullable`1[System.DateTime]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDateTime()); break; case "System.Boolean": case "System.Nullable`1[System.Boolean]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToBool()); break; case "System.Byte": case "System.Nullable`1[System.Byte]": mapPropertyInfoDict[j].SetValue(entity, Byte.Parse(row.GetCell(j).ParseToString())); break; case "System.Int16": case "System.Nullable`1[System.Int16]": mapPropertyInfoDict[j].SetValue(entity, Int16.Parse(row.GetCell(j).ParseToString())); break; case "System.Int32": case "System.Nullable`1[System.Int32]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToInt()); break; case "System.Int64": case "System.Nullable`1[System.Int64]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToLong()); break; case "System.Double": case "System.Nullable`1[System.Double]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble()); break; case "System.Decimal": case "System.Nullable`1[System.Decimal]": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDecimal()); break; default: case "System.String": mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString()); break; } } } } list.Add(entity); } hssfWorkbook?.Close(); xssWorkbook?.Close(); return(list); }
public void TestThemesTableColors() { // Load our two test workbooks XSSFWorkbook simple = XSSFTestDataSamples.OpenSampleWorkbook(testFileSimple); XSSFWorkbook complex = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex); // Save and re-load them, to check for stability across that XSSFWorkbook simpleRS = XSSFTestDataSamples.WriteOutAndReadBack(simple) as XSSFWorkbook; XSSFWorkbook complexRS = XSSFTestDataSamples.WriteOutAndReadBack(complex) as XSSFWorkbook; // Fetch fresh copies to test with simple = XSSFTestDataSamples.OpenSampleWorkbook(testFileSimple); complex = XSSFTestDataSamples.OpenSampleWorkbook(testFileComplex); // Files and descriptions Dictionary <String, XSSFWorkbook> workbooks = new Dictionary <String, XSSFWorkbook>(); workbooks.Add(testFileSimple, simple); workbooks.Add("Re-Saved_" + testFileSimple, simpleRS); workbooks.Add(testFileComplex, complex); workbooks.Add("Re-Saved_" + testFileComplex, complexRS); // Sanity check //Assert.AreEqual(rgbExpected.Length, rgbExpected.Length); // For offline testing bool createFiles = false; // Check each workbook in turn, and verify that the colours // for the theme-applied cells in Column A are correct foreach (String whatWorkbook in workbooks.Keys) { XSSFWorkbook workbook = workbooks[whatWorkbook]; XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet; int startRN = 0; if (whatWorkbook.EndsWith(testFileComplex)) { startRN++; } for (int rn = startRN; rn < rgbExpected.Length + startRN; rn++) { XSSFRow row = sheet.GetRow(rn) as XSSFRow; Assert.IsNotNull(row, "Missing row " + rn + " in " + whatWorkbook); String ref1 = (new CellReference(rn, 0)).FormatAsString(); XSSFCell cell = row.GetCell(0) as XSSFCell; Assert.IsNotNull(cell, "Missing cell " + ref1 + " in " + whatWorkbook); int expectedThemeIdx = rn - startRN; ThemeElement themeElem = ThemeElement.ById(expectedThemeIdx); Assert.AreEqual(themeElem.name.ToLower(), cell.StringCellValue, "Wrong theme at " + ref1 + " in " + whatWorkbook); // Fonts are theme-based in their colours XSSFFont font = (cell.CellStyle as XSSFCellStyle).GetFont(); CT_Color ctColor = font.GetCTFont().GetColorArray(0); Assert.IsNotNull(ctColor); Assert.AreEqual(true, ctColor.IsSetTheme()); Assert.AreEqual(themeElem.idx, ctColor.theme); // Get the colour, via the theme XSSFColor color = font.GetXSSFColor(); // Theme colours aren't tinted Assert.AreEqual(color.HasTint, false); // Check the RGB part (no tint) Assert.AreEqual(rgbExpected[expectedThemeIdx], HexDump.EncodeHexString(color.RGB), "Wrong theme colour " + themeElem.name + " on " + whatWorkbook); long themeIdx = font.GetCTFont().GetColorArray(0).theme; Assert.AreEqual(expectedThemeIdx, themeIdx, "Wrong theme index " + expectedThemeIdx + " on " + whatWorkbook ); if (createFiles) { XSSFCellStyle cs = row.Sheet.Workbook.CreateCellStyle() as XSSFCellStyle; cs.SetFillForegroundColor(color); cs.FillPattern = FillPatternType.SolidForeground; row.CreateCell(1).CellStyle = (cs); } } if (createFiles) { FileStream fos = new FileStream("Generated_" + whatWorkbook, FileMode.Create, FileAccess.ReadWrite); workbook.Write(fos); fos.Close(); } } }
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 string EXPOST_TJBB_RY_ZC_SELECT(string datastring) { MES_RETURN_UI rst = new MES_RETURN_UI(); string token = AppClass.GetSession("token").ToString(); int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID")); HR_RY_ZC model = Newtonsoft.Json.JsonConvert.DeserializeObject <HR_RY_ZC>(datastring); HR_RY_ZC_SELECT result = hrmodels.RY_RYINFO_RSDA.RY_ZC_SELECT(model, token); if (result.MES_RETURN.TYPE == "E") { return(Newtonsoft.Json.JsonConvert.SerializeObject(result.MES_RETURN)); } try { FileStream file = new FileStream(Server.MapPath("~") + @"/Areas/HR/ExportFile/导出模版.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = new XSSFWorkbook(file); ISheet sheet = workbook.GetSheetAt(0); int rowcount = 0; string tt = "来源,职称名称,职称系列,获取日期,机关(部门),证件编号,复审日期,聘用日期,聘用系列,聘用等级,工号,姓名,公司,归属部门,备注"; string[] ttlist = tt.Split(','); IRow rowtt = sheet.CreateRow(rowcount++); int cellIndex = 0; for (int a = 0; a < ttlist.Length; a++) { rowtt.CreateCell(cellIndex++).SetCellValue(ttlist[a]); } DataTable dtinfo = result.DATALIST; for (int i = 0; i < dtinfo.Rows.Count; i++) { cellIndex = 0; IRow row = sheet.CreateRow(rowcount++); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["ZCLBNAME"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["ZCNAME"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["ZCXLNAME"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["ZCDATE"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["ZCJGBM"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["ZCNO"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["FSDATE"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["PYRQ"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["PYXLNAME"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["PYLEVELNAME"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["GH"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["YGNAME"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["GSNAME"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["GSBMNAME"].ToString()); row.CreateCell(cellIndex++).SetCellValue(dtinfo.Rows[i]["REMARK"].ToString()); } string now = DateTime.Now.ToString("yyyyMMddHHmmss.fff"); FileStream file1 = new FileStream(string.Format(@"{0}/Areas/HR/ExportFile/{1}.xlsx", Server.MapPath("~"), now), FileMode.Create); workbook.Write(file1); file1.Close(); rst.TYPE = "S"; rst.MESSAGE = now; } catch { rst.TYPE = "E"; rst.MESSAGE = "生成文件失败!"; } return(Newtonsoft.Json.JsonConvert.SerializeObject(rst)); }
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 ActionResult Upload(HttpPostedFileBase uploadfile) { if (Request.Files["uploadfile"].ContentLength > 0) { string extension = System.IO.Path.GetExtension(uploadfile.FileName); if (extension == ".xls" || extension == ".xlsx") { string fileLocation = Server.MapPath("~/Content/") + Request.Files["uploadfile"].FileName; if (System.IO.File.Exists(fileLocation)) // 驗證檔案是否存在 { System.IO.File.Delete(fileLocation); } Request.Files["uploadfile"].SaveAs(fileLocation); // 存放檔案到伺服器上 // 建立一個工作簿 XSSFWorkbook excel; // 檔案讀取 using (FileStream files = new FileStream(fileLocation, FileMode.Open, FileAccess.Read)) { excel = new XSSFWorkbook(files); // 將剛剛的Excel 讀取進入到工作簿中 } // Excel 的哪一個活頁簿,有兩種方式可以取得活頁簿 ISheet sheet = excel.GetSheetAt(0); // 在第幾個活頁簿,饅頭建議使用,畢竟我們不知道使用者會把活頁部取神麼名字,先抓地一個在說!(從0開始計算) for (int row = 1; row <= sheet.LastRowNum; row++) // 使用For 走訪所有的資料列 { if (sheet.GetRow(row) != null) // 驗證是不是空白列 { Color _npoi = new Color(); for (int c = 0; c <= sheet.GetRow(row).LastCellNum; c++) // 使用For 走訪資料欄 { switch (c) { case 0: _npoi.ColorID = sheet.GetRow(row).GetCell(c).RowIndex; break; case 1: _npoi.ColorName = sheet.GetRow(row).GetCell(c).StringCellValue; break; case 2: _npoi.R = Convert.ToInt32 (sheet.GetRow(row).GetCell(c).NumericCellValue); break; case 3: _npoi.G = Convert.ToInt32 (sheet.GetRow(row).GetCell(c).NumericCellValue); break; case 4: _npoi.B = Convert.ToInt32 (sheet.GetRow(row).GetCell(c).NumericCellValue); break; } } db.Entry(_npoi).State = System.Data.Entity.EntityState.Modified; db.SaveChanges(); } } } } return(this.RedirectToAction("Index")); }
public IEnumerable <T> Import <T>(Stream input) { List <T> objs = new List <T>(); XSSFWorkbook workbook = new XSSFWorkbook(input); ISheet sheet = workbook.GetSheetAt(0); var rows = sheet.GetRowEnumerator(); int index = 0; while (index++ < this.DataRowStart) //移动到数据起始行 { if (!rows.MoveNext()) { return(objs); } } while (rows.MoveNext()) { var row = (IRow)rows.Current; T obj = Activator.CreateInstance <T>(); int i = 0; //当遇到第一列为空的行时退出 if (row.Cells[0] == null || string.IsNullOrEmpty(row.Cells[0].ToString())) { break; } var properties = obj.GetType().GetProperties(); foreach (var property in properties) { var cell = row.GetCell(i++); if (cell != null) { //时间字段不能使用ToString()获取 object value = null; try { switch (property.PropertyType.Name) { case "DateTime": { if (cell.CellType != CellType.BLANK) { value = cell.DateCellValue; } break; } case "Int32": { value = cell.NumericCellValue; break; } default: { value = cell.ToString(); break; } } property.SetValue(obj, Convert.ChangeType(value, property.PropertyType), null); } catch { } } } objs.Add(obj); } return(objs); }
private void ExportClass(string fileName, string exportDir, string csHead) { XSSFWorkbook xssfWorkbook; using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { xssfWorkbook = new XSSFWorkbook(file); } string protoName = Path.GetFileNameWithoutExtension(fileName); string exportPath = Path.Combine(exportDir, $"{protoName}.cs"); using (FileStream txt = new FileStream(exportPath, FileMode.Create)) using (StreamWriter sw = new StreamWriter(txt)) { StringBuilder sb = new StringBuilder(); ISheet sheet = xssfWorkbook.GetSheetAt(0); sb.Append(csHead); sb.Append($"\t[Config((int)({GetCellString(sheet, 0, 0)}))]\n"); sb.Append($"\tpublic partial class {protoName}Category : AddressableCategory<{protoName}>\n"); sb.Append("\t{\n"); sb.Append("\t}\n\n"); sb.Append($"\tpublic class {protoName}: IConfig\n"); sb.Append("\t{\n"); sb.Append("\t\tpublic long Id { get; set; }\n"); int cellCount = sheet.GetRow(3).LastCellNum; for (int i = 2; i < cellCount; i++) { string fieldDesc = GetCellString(sheet, 2, i); if (fieldDesc.StartsWith("#")) { continue; } // s开头表示这个字段是服务端专用 if (fieldDesc.StartsWith("s") && this.isClient) { continue; } string fieldName = GetCellString(sheet, 3, i); if (fieldName == "Id" || fieldName == "_id") { continue; } string fieldType = GetCellString(sheet, 4, i); if (fieldType == "" || fieldName == "") { continue; } sb.Append($"\t\tpublic {fieldType} {fieldName};\n"); } sb.Append("\t}\n"); sb.Append("}\n"); sw.Write(sb.ToString()); } }
public void setSheet(int sheetNumber) { sheet = workbook.GetSheetAt(sheetNumber) as XSSFSheet; }
/// <summary> /// 读取XLS文件到DataTable中,支持Excel 2003或以上 /// </summary> /// <param name="filePathAndName">Xls或Xlsx的文件路径</param> /// <param name="useTitle">第一行是否是标题</param> /// <exception cref="System.ArgumentNullException"></exception> /// <exception cref="System.FileNotFoundException"></exception> /// <returns></returns> public static DataSet ReadToDataSet(string filePathAndName, bool useTitle = true) { if (string.IsNullOrWhiteSpace(filePathAndName)) { throw new ArgumentNullException("filePathAndName"); } if (!File.Exists(filePathAndName)) { throw new FileNotFoundException("文件不存在", filePathAndName); } DataSet ds = new DataSet(); FileStream fs = new FileStream(filePathAndName, FileMode.Open, FileAccess.Read); var bytes = new byte[fs.Length]; fs.Read(bytes, 0, bytes.Length); fs.Close(); IWorkbook wk = null; try { wk = new HSSFWorkbook(new MemoryStream(bytes)); } catch (Exception exp) { try { wk = new XSSFWorkbook(new MemoryStream(bytes)); } catch { return(ds); } } for (int i = 0; i != wk.NumberOfSheets; i++) { ISheet sheet = wk.GetSheetAt(i); DataTable dt = new DataTable(); dt.TableName = sheet.SheetName; for (int j = 0; j <= sheet.LastRowNum; j++) { IRow row = sheet.GetRow(j); if (row == null) { continue; } row.Cells.ForEach(delegate(ICell cell) { if (cell.CellType != CellType.String) { cell.SetCellType(CellType.String); } }); string value = null; //区域名,表头 if (j == 0 && useTitle) { for (int k = 0; k < row.Cells.Count; k++) { value = row.GetCell(k) == null ? ("column" + j) : row.GetCell(k).StringCellValue; if (value != null) { dt.Columns.Add(value); } } } else { object[] datas = new object[dt.Columns.Count]; for (int k = 0; k < datas.Length; k++) { datas[k] = row.GetCell(k) == null ? null : row.GetCell(k).StringCellValue; } dt.Rows.Add(datas); } } if (dt.Rows.Count != 0) { ds.Tables.Add(dt); } } return(ds); }
public static List <T> ReadFromFile <T>(string filename) where T : class, new() { string excelFileName = filename; IWorkbook workbook = new XSSFWorkbook(excelFileName); ISheet sheet = workbook.GetSheetAt(0); List <T> configInfos = new List <T>(); Dictionary <PropertyInfo, int> cellNumbs = new Dictionary <PropertyInfo, int>(); var props = typeof(T).GetProperties(); foreach (PropertyInfo prop in props) { if (prop.GetCustomAttribute <NotMappedAttribute>() == null) { cellNumbs[prop] = 0; } } for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { var row = sheet.GetRow(i); if (row == null) { continue; } if (i == 1) { foreach (var cell in row.Cells) { string cellName = cell.StringCellValue; if (string.IsNullOrEmpty(cellName)) { continue; } string propName = cellName.Substring(0, cellName.IndexOf(":", StringComparison.Ordinal)); var prop = cellNumbs.Keys.FirstOrDefault(p => p.Name == propName); if (prop != null) { cellNumbs[prop] = cell.ColumnIndex; } } } else { var ladderConfig = new T(); bool failed = false; foreach (var cellPair in cellNumbs) { var cell = row.GetCell(cellPair.Value); if (cell == null) { continue; //throw new Exception($"第{row.RowNum}行 缺失列{cellPair.Key.Name}:{cellPair.Value}"); } try { if (cellPair.Key.PropertyType == typeof(int)) { if (cell.CellType == CellType.String) { int numb = int.Parse(cell.StringCellValue); cellPair.Key.SetValue(ladderConfig, numb); } else { cellPair.Key.SetValue(ladderConfig, Convert.ToInt32(cell.NumericCellValue)); } } else if (cellPair.Key.PropertyType == typeof(double)) { cellPair.Key.SetValue(ladderConfig, Convert.ToDouble(cell.NumericCellValue)); } else if (cellPair.Key.PropertyType == typeof(float)) { cellPair.Key.SetValue(ladderConfig, Convert.ToSingle(cell.NumericCellValue)); } else if (cellPair.Key.PropertyType == typeof(bool)) { cellPair.Key.SetValue(ladderConfig, Convert.ToBoolean(cell.NumericCellValue)); } //else if (cellPair.Key.PropertyType == typeof(int[])) //{ // string str = Convert.ToString(cell.StringCellValue).Trim(new[] { '[', ']' }); // cellPair.Key.SetValue(ladderConfig, str); // //string[] val = str.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries); // //int[] valInts = val.Select(int.Parse).ToArray(); // //cellPair.Key.SetValue(ladderConfig, valInts); //} else if (cellPair.Key.PropertyType == typeof(string)) { cellPair.Key.SetValue(ladderConfig, cell.StringCellValue); } else if (cellPair.Key.PropertyType == typeof(TimeSpan?)) { if (!string.IsNullOrEmpty(cell.StringCellValue)) { TimeSpan ts = TimeSpan.Parse(cell.StringCellValue); cellPair.Key.SetValue(ladderConfig, ts); string[] hms = cell.StringCellValue.Substring(cell.StringCellValue.IndexOf(".") + 1).Split(":"); int h = int.Parse(hms[0]); int m = int.Parse(hms[1]); int s = int.Parse(hms[2]); if (h >= 24 || m >= 60 || s >= 60) { throw new Exception($"表格{filename} 第{cell.RowIndex}行 第{cellPair.Key.Name}:{cellPair.Value}列 [{ cell.StringCellValue }]时间格式不对"); } } } else if (cellPair.Key.PropertyType == typeof(DateTime?)) { if (!string.IsNullOrEmpty(cell.StringCellValue)) { DateTime ts = DateTime.Parse(cell.StringCellValue); cellPair.Key.SetValue(ladderConfig, ts); } } else if (cellPair.Key.PropertyType == typeof(JsonObject <int[]>)) { if (!string.IsNullOrEmpty(cell.StringCellValue)) { var arr = cell.StringCellValue.Trim(new[] { '[', ']' }).Split(','); var ts = arr.Where(x => !string.IsNullOrEmpty(x)).Select(int.Parse).ToArray(); cellPair.Key.SetValue(ladderConfig, new JsonObject <int[]>(ts)); } } else if (cellPair.Key.PropertyType == typeof(JsonObject <List <int> >)) { if (!string.IsNullOrEmpty(cell.StringCellValue)) { var arr = cell.StringCellValue.Trim(new[] { '[', ']' }).Split(','); var ts = arr.Where(x => !string.IsNullOrEmpty(x)).Select(int.Parse).ToList(); cellPair.Key.SetValue(ladderConfig, new JsonObject <List <int> >(ts)); } } else { throw new Exception("不支持的类型"); } } catch (Exception ex) { throw new Exception($"表格{filename} 第{cell.RowIndex}行 第{cellPair.Key.Name}:{cellPair.Value}列 [{ cell.StringCellValue }]有问题请查看", ex); } } if (!failed) { configInfos.Add(ladderConfig); } } } return(configInfos); }
public void TestReadTextBoxParagraphs() { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithTextBox.xlsx"); XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet; //the sheet has one relationship and it is XSSFDrawing List <POIXMLDocumentPart.RelationPart> rels = sheet.RelationParts; Assert.AreEqual(1, rels.Count); POIXMLDocumentPart.RelationPart rp = rels[0]; Assert.IsTrue(rp.DocumentPart is XSSFDrawing); XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart; //sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing Assert.AreSame(drawing, sheet.CreateDrawingPatriarch()); String drawingId = rp.Relationship.Id; //there should be a relation to this Drawing in the worksheet Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing()); Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id); List <XSSFShape> shapes = drawing.GetShapes(); Assert.AreEqual(1, shapes.Count); Assert.IsTrue(shapes[0] is XSSFSimpleShape); XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[0]; List <XSSFTextParagraph> paras = textbox.TextParagraphs; Assert.AreEqual(3, paras.Count); Assert.AreEqual("Line 2", paras[1].Text); // check content of second paragraph Assert.AreEqual("Line 1\nLine 2\nLine 3", textbox.Text); // check content of entire textbox // check attributes of paragraphs Assert.AreEqual(TextAlign.LEFT, paras[0].TextAlign); Assert.AreEqual(TextAlign.CENTER, paras[1].TextAlign); Assert.AreEqual(TextAlign.RIGHT, paras[2].TextAlign); var clr = paras[0].TextRuns[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 255, 0, 0 }, new int[] { clr.R, clr.G, clr.B })); clr = paras[1].TextRuns[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 255, 0 }, new int[] { clr.R, clr.G, clr.B })); clr = paras[2].TextRuns[0].FontColor; Assert.IsTrue(Arrays.Equals( new int[] { 0, 0, 255 }, new int[] { clr.R, clr.G, clr.B })); checkRewrite(wb); wb.Close(); }
/// <summary> /// 由Excel导入DataTable /// </summary> /// <param name="excelFileStream">Excel文件流</param> /// <param name="sheetName">Excel工作表索引</param> /// <param name="headerRowIndex">Excel表头行索引</param> /// <returns>DataTable</returns> private static DataTable Excel2DataTable(Stream excelFileStream, int sheetIndex, int headerRowIndex) { XSSFWorkbook workbook = new XSSFWorkbook(excelFileStream); XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(sheetIndex); DataTable table = new DataTable(); table.TableName = sheet.SheetName; XSSFRow headerRow = (XSSFRow)sheet.GetRow(headerRowIndex); XSSFRow firstRow = null; if (headerRowIndex + 1 >= sheet.FirstRowNum && headerRowIndex + 1 <= sheet.LastRowNum) { firstRow = (XSSFRow)sheet.GetRow(headerRowIndex + 1); } int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { if (headerRow.GetCell(i) == null || headerRow.GetCell(i).ToString() == "") { // 如果遇到第一个空列,则不再继续向后读取 cellCount = i + 1; break; } Type columnType = typeof(string); if (firstRow != null && firstRow.GetCell(i) != null && firstRow.GetCell(i).CellType == CellType.Numeric) { columnType = typeof(double); } DataColumn column = new DataColumn(headerRow.GetCell(i).ToString(), columnType); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "") { // 如果遇到第一个空行,则不再继续向后读取 break; } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { ICell cell = row.GetCell(j); if (cell == null) { continue; } else if (cell.CellType == CellType.Numeric) { dataRow[j] = row.GetCell(j).NumericCellValue; } else { dataRow[j] = row.GetCell(j).ToString(); } } table.Rows.Add(dataRow); } return(table); }