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);
}
