public void TestLoadSample()
{
XSSFWorkbook workbook = new XSSFWorkbook(_ssSampels.OpenResourceAsStream("sample.xlsx"));
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.AreEqual("Sheet1", workbook.GetSheetName(0));
ISheet sheet = workbook.GetSheetAt(0);
IRow row = sheet.GetRow(0);
ICell cell = row.GetCell((short)1);
Assert.IsNotNull(cell);
Assert.AreEqual(111.0, cell.NumericCellValue, 0.0);
cell = row.GetCell((short)0);
Assert.AreEqual("Lorem", cell.RichStringCellValue.String);
}
public void Bug47813()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("47813.xlsx");
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.IsNotNull(wb.GetCalculationChain());
Assert.AreEqual("Numbers", wb.GetSheetName(0));
//the second sheet is of type 'chartsheet'
Assert.AreEqual("Chart", wb.GetSheetName(1));
Assert.IsTrue(wb.GetSheetAt(1) is XSSFChartSheet);
Assert.AreEqual("SomeJunk", wb.GetSheetName(2));
wb.RemoveSheetAt(2);
Assert.AreEqual(2, wb.NumberOfSheets);
Assert.IsNull(wb.GetCalculationChain());
wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(2, wb.NumberOfSheets);
Assert.IsNull(wb.GetCalculationChain());
Assert.AreEqual("Numbers", wb.GetSheetName(0));
Assert.AreEqual("Chart", wb.GetSheetName(1));
}
public List <string> GetExcelSheetName()
{
IWorkbook workbook = null; //新建IWorkbook對象
string fileName = string.Concat(AppSettingConfig.FilePath(), "/", AppSettingConfig.StoreManageFileName());
FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
workbook = new XSSFWorkbook(fileStream); //xlsx數據讀入workbook
var sheetListName = new List <string>();
for (int sheetCount = 1; sheetCount < workbook.NumberOfSheets; sheetCount++)
{
var sheetName = workbook.GetSheetName(sheetCount); //獲取第i個工作表
sheetListName.Add(sheetName);
}
return(sheetListName);
}
public DataTable GetDataFromRuleBook(XSSFWorkbook wb)
{
ISheet sheet = wb.GetSheet(wb.GetSheetName(0));
BindingSource source = new BindingSource();
surnameDT.Rows.Clear();
surnameDT.Columns.Clear();
for (int i = 0; i < sheet.LastRowNum; i++)
{
// add neccessary columns
if (surnameDT.Columns.Count < sheet.GetRow(i).Cells.Count)
{
for (int j = 0; j < sheet.GetRow(i).Cells.Count; j++)
{
surnameDT.Columns.Add("", typeof(string));
}
}
// add row
surnameDT.Rows.Add();
// write row value
for (int j = 0; j < sheet.GetRow(i).Cells.Count; j++)
{
var cell = sheet.GetRow(i).GetCell(j);
if (cell != null)
{
// TODO: you can add more cell types capatibility, e. g. formula
switch (cell.CellType)
{
case CellType.Numeric:
surnameDT.Rows[i][j] = sheet.GetRow(i).GetCell(j).NumericCellValue;
break;
case CellType.String:
surnameDT.Rows[i][j] = sheet.GetRow(i).GetCell(j).StringCellValue;
break;
}
}
}
}
return(surnameDT);
}
/// <summary>
/// .xlsx后缀的Excel文件添加图片
/// </summary>
/// <param name="excelPath"></param>
/// <param name="imgPath"></param>
public static void InsertImageToXLSXExcel(string excelPath, string imgPath)
{
try
{
using (FileStream fs = new FileStream(excelPath, FileMode.Open))//获取指定Excel文件流
{
//创建工作簿
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
//获取第一个工作表（下标从0起）
XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheet(xssfworkbook.GetSheetName(0));
//获取指定图片的字节流
byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
//将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
//图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
//同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.JPEG);
//创建画布
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//设置图片坐标与大小
//函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
//坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第五列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
//坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
//坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
//坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 5, 2, 10, 3);
//正式在指定位置插入图片
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//创建一个新的Excel文件流，可以和原文件名不一样，
//如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
FileStream file = new FileStream(excelPath, FileMode.Create);
//将已插入图片的Excel流写入新创建的Excel中
xssfworkbook.Write(file);
//关闭工作簿
xssfworkbook.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private static String getExcelSheetFirstName2007(string excelFilePath)
{
String sheetName = null;
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
XSSFWorkbook workbook = new XSSFWorkbook(stream);
try
{
ISheet sheet = workbook.GetSheetAt(0);
sheetName = workbook.GetSheetName(0);
}
catch (Exception ex)
{
}
}
return(sheetName);
}
/// <summary>
/// 获取sheet表名
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static string[] GetSheetName(string filePath)
{
try
{
int sheetNumber = 0;
var file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (filePath.IndexOf(".xlsx") > 0)
{
//2007版本
var xssfworkbook = new XSSFWorkbook(file);
sheetNumber = xssfworkbook.NumberOfSheets;
string[] sheetNames = new string[sheetNumber];
for (int i = 0; i < sheetNumber; i++)
{
sheetNames[i] = xssfworkbook.GetSheetName(i);
}
return(sheetNames);
}
else if (filePath.IndexOf(".xls") > 0)
{
//2003版本
var hssfworkbook = new HSSFWorkbook(file);
sheetNumber = hssfworkbook.NumberOfSheets;
string[] sheetNames = new string[sheetNumber];
for (int i = 0; i < sheetNumber; i++)
{
sheetNames[i] = hssfworkbook.GetSheetName(i);
}
return(sheetNames);
}
return(null);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(null);
}
}
public static List <TOCScreenshot> ReadTOC(string TOCPath)
{
if (string.IsNullOrWhiteSpace(TOCPath) || !File.Exists(TOCPath))
{
throw new ArgumentException("Blank or non-existant path given", nameof(TOCPath));
}
try
{
var res = new List <TOCScreenshot>();
XSSFWorkbook tocWorkbook;
var sheetNames = new List <string>();
using (FileStream file = new FileStream(TOCPath, FileMode.Open, FileAccess.Read))
{
tocWorkbook = new XSSFWorkbook(file);
for (int i = 1; i < tocWorkbook.NumberOfSheets; i++)
{
sheetNames.Add(tocWorkbook.GetSheetName(i));
}
}
var tocChoices = new ExcelMapper(@TOCPath)
{
TrackObjects = false
};
foreach (var sheetName in sheetNames)
{
var screenshot = new TOCScreenshot()
{
Filename = $"{sheetName}.png"
};
screenshot.Choices = tocChoices.Fetch <TOCChoice>(sheetName).ToList();
res.Add(screenshot);
}
return(res);
}
catch (Exception x)
{
x.Data.Add(nameof(TOCPath), TOCPath);
throw;
}
}
public DataSet ConvertToDataTable()
{
DataSet ds = new DataSet();
string sheetName = String.Empty;
for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
{
sheetName = hssfworkbook.GetSheetName(i);
try
{
DataTable dt = getDataTable(sheetName, true);
ds.Tables.Add(dt);
}
catch (Exception ex)
{
//对于异常的Excel工作表，不导入
}
}
return(ds);
}
public static ExcelFileModel ExcleFileToDataTable(HttpPostedFileBase file, string serverPath)
{
ExcelFileModel model = new ExcelFileModel();
//文件名
var fileName = file.FileName;
if (string.Empty.Equals(fileName))
{
model.Code = 1;
model.Msg = "文件不能为空！";
return(model);
}
//后缀名
var fileextension = fileName.Substring(fileName.LastIndexOf('.'));
//获取新文件名
var uuid = Guid.NewGuid().ToString();
var newFileName = uuid + fileextension;
var savepath = Path.Combine(serverPath, newFileName);
//1、保存excel文件
file.SaveAs(savepath);
//2、读取excel文件
FileStream fs = new FileStream(savepath, FileMode.Open, FileAccess.Read);
IWorkbook book = new XSSFWorkbook(fs);
ISheet sheet = book.GetSheet(book.GetSheetName(0));
//读取图片
List <PicturesInfo> list = new List <PicturesInfo>();
list = NpoiExtend.GetAllPictureInfos(sheet);
//读取文字
DataTable dt = new DataTable();
ExcelHelper excelHelper = new ExcelHelper(file.FileName, savepath);
dt = excelHelper.ExcelToDataTable("sheet1", true);
model.Code = 0;
model.PicList = list;
model.InfoList = dt;
model.SavePath = savepath;
return(model);
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="ExcelFileStream">Excel文件流</param>
/// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(string fileName, Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
{
//XSSFWorkbook
string extendsion = Path.GetExtension(fileName).TrimStart('.');
IWorkbook workbook = null;
switch (extendsion)
{
case "xls":
workbook = new HSSFWorkbook(ExcelFileStream);
break;
case "xlsx":
workbook = new XSSFWorkbook(ExcelFileStream);
break;
}
ExcelFileStream.Close();
string SheetName = workbook.GetSheetName(SheetIndex);
return(RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex));
}
public static List <SheetInfoClass> GetExcelSheetStruct(HttpPostedFileBase httpPostedFileBase)
{
ISheet sheet;
string fileName = httpPostedFileBase.FileName;
var sheetInfoClassList = new List <SheetInfoClass>();
using (Stream file = httpPostedFileBase.InputStream)
{
//读取2007
if (fileName.Contains(".xlsx"))
{
var xssfworkbook = new XSSFWorkbook(file);
int total = xssfworkbook.NumberOfSheets;
for (int i = 0; i < total; i++)
{
var sheetInfoClass = new SheetInfoClass
{
Id = i,
Name = xssfworkbook.GetSheetName(i)
};
sheetInfoClassList.Add(sheetInfoClass);
}
}
else
{
var hssfworkbook = new HSSFWorkbook(file);
int total = hssfworkbook.NumberOfSheets;
for (int i = 0; i < total; i++)
{
var sheetInfoClass = new SheetInfoClass
{
Id = i,
Name = hssfworkbook.GetSheetName(i)
};
sheetInfoClassList.Add(sheetInfoClass);
}
}
}
return(sheetInfoClassList);
}
public static async Task ParseXLS(string workBookPath)
{
try
{
ISheet sheet;
var fileExt = Path.GetExtension(workBookPath);
var fileFullPath = Path.GetFullPath(workBookPath);
if (fileExt != null && fileExt.ToLower() == ".xls")
{
HSSFWorkbook hssfwb;
using (var file = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read))
hssfwb = new HSSFWorkbook(file);
sheet = hssfwb.GetSheet(hssfwb.GetSheetName(0));
await MainCyrcle(sheet);
using (var file = new FileStream(fileFullPath, FileMode.Create))
hssfwb.Write(file);
}
else if (fileExt != null && fileExt.ToLower() == ".xlsx")
{
XSSFWorkbook xssfwb;
using (var file = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read))
xssfwb = new XSSFWorkbook(file);
sheet = xssfwb.GetSheet(xssfwb.GetSheetName(0));
await MainCyrcle(sheet);
using (var file = new FileStream(fileFullPath, FileMode.Create))
xssfwb.Write(file);
}
Informer.RaiseOnResultReceived($"{workBookPath} successfully saved");
}
catch (Exception ex)
{
Informer.RaiseOnResultReceived(ex);
}
}
/// <summary>
/// 读取 xlsx 文件，转换为 DataSet
/// </summary>
/// <param name="filePath">工作簿文件路径</param>
/// <param name="sheetName">工作表名</param>
/// <returns></returns>
public static DataTable?ReadDataTable(string filePath, string sheetName)
{
XSSFWorkbook xssfWorkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
xssfWorkbook = new XSSFWorkbook(file);
}
for (int n = 0; n < xssfWorkbook.NumberOfSheets; n++)
{
if (string.Equals(xssfWorkbook.GetSheetName(n), sheetName, StringComparison.OrdinalIgnoreCase) == false)
{
continue;
}
NPOI.SS.UserModel.ISheet sheet = xssfWorkbook.GetSheetAt(n);
return(ToTable(sheet));
}
return(null);
}
/// <summary>
/// 获取Excel所有工作表的名称
/// </summary>
/// <param name="path">Excel文件名</param>
/// <returns>返回所有工作表的名称</returns>
public string[] GetSheetName(string path)
{
int sheetNum = 0;
//读取Excel为文件流
FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
//操作Excel文件对象
Workbook = new XSSFWorkbook(fs);
//获取Excel表的数量
sheetNum = Workbook.NumberOfSheets;
//根据索引获取工作表
string[] result = new string[sheetNum];
for (int i = 0; i < sheetNum; i++)
{
result[i] = Workbook.GetSheetName(i);
}
return(result);
}
private void groupDataFileButton_Click(object sender, EventArgs e)
{
string filename = FileHelper.OpenFileDialog("请选择简报数据文件", "xlsx文件|*.xlsx");
if (filename != "")
{
this.groupDataFileText.Text = filename;
}
else
{
MsgBox.ShowError("请选择有效文件！");
return;
}
groupCombo.Items.Clear(); //清空Combo中的所有项
//开始读取Sheet名称
XSSFWorkbook workbook;
try
{
using (FileStream stream = File.OpenRead(filename))
{
workbook = new XSSFWorkbook(stream);
}
}
catch (Exception ex)
{
MsgBox.ShowError("错误: " + ex.Message);
this.groupDataFileText.Text = "";
return;
}
int SheetCount = workbook.NumberOfSheets; //获取表的数量
for (int i = 0; i < SheetCount; i++) //逐个加入Combo
{
groupCombo.Items.Add(workbook.GetSheetName(i));
}
workbook.Clear(); //清空workbook，释放内存。
}
string[] GetSheets()
{
List <string> sheetNames = new List <string>();
for (ushort i = 0; i < workBook.Count; i++)
{
var name = workBook.GetSheetName(i);
if (string.IsNullOrEmpty(name))
{
break;
}
if (regInvalidSheetName.IsMatch(name))
{
// 跳过没起名字的Sheet页面
continue;
}
if (regValidSheetName.IsMatch(name))
{
sheetNames.Add(name);
}
}
return(sheetNames.ToArray());
}
/// <summary>
/// 查詢Excel中有多少個sheet，并返回名稱
/// </summary>
/// <param name="fileName">Excel文件，完整路徑</param>
/// <returns>返回字符串數組，包含所有的sheet名稱</returns>
public static string[] GetSheetNameFromExcel(string fileName)
{
try
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fileName);
int sheetQty = xssfworkbook.NumberOfSheets;
string[] sheetNames = new string[sheetQty];
for (int i = 0; i < sheetQty; i++)
{
sheetNames[i] = xssfworkbook.GetSheetName(i);
}
xssfworkbook.Close();
xssfworkbook = null;
return(sheetNames);
}
catch (Exception ex)
{
return(null);
}
finally
{
}
}
/// <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 string getSheets()
{
return(workbook.GetSheetName(0));
}
public string GetSheetName(int sheet)
{
return(XssfWorkbook.GetSheetName(sheet));
}
public string UploadFile()
{
if (HttpContext.Current.Request.Files.AllKeys.Any())
{
logger.Info("start Item Upload Exel File: ");
var identity = User.Identity as ClaimsIdentity;
int compid = 0, userid = 0;
// Access claims
foreach (Claim claim in identity.Claims)
{
if (claim.Type == "compid")
{
compid = int.Parse(claim.Value);
}
if (claim.Type == "userid")
{
userid = int.Parse(claim.Value);
}
}
// Get the uploaded image from the Files collection
System.Web.HttpPostedFile httpPostedFile = HttpContext.Current.Request.Files["file"];
if (httpPostedFile != null)
{
// Validate the uploaded image(optional)
byte[] buffer = new byte[httpPostedFile.ContentLength];
using (BinaryReader br = new BinaryReader(httpPostedFile.InputStream))
{
br.Read(buffer, 0, buffer.Length);
}
XSSFWorkbook hssfwb;
using (MemoryStream memStream = new MemoryStream())
{
BinaryFormatter binForm = new BinaryFormatter();
memStream.Write(buffer, 0, buffer.Length);
memStream.Seek(0, SeekOrigin.Begin);
hssfwb = new XSSFWorkbook(memStream);
string sSheetName = hssfwb.GetSheetName(0);
ISheet sheet = hssfwb.GetSheet(sSheetName);
AuthContext context = new AuthContext();
IRow rowData;
ICell cellData = null;
try
{
List <Supplier> supCollection = new List <Supplier>();
for (int iRowIdx = 0; iRowIdx <= sheet.LastRowNum; iRowIdx++) // iRowIdx = 0; HeaderRow
{
if (iRowIdx == 0)
{
rowData = sheet.GetRow(iRowIdx);
if (rowData != null)
{
string field = string.Empty;
field = rowData.GetCell(1).ToString().Trim();
if (field != "SUPPLIERCODES")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(2).ToString().Trim();
if (field != "Name")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(3).ToString().Trim();
if (field != "Brand")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(4).ToString().Trim();
if (field != "MobileNo")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(5).ToString().Trim();
if (field != "OfficePhone")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(6).ToString().Trim();
if (field != "BillingAddress")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(7).ToString().Trim();
if (field != "TINNo")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(8).ToString().Trim();
if (field != "Bank_AC_No")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(9).ToString().Trim();
if (field != "Bank_Name")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(10).ToString().Trim();
if (field != "Bank_Ifsc")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(11).ToString().Trim();
if (field != "ShippingAddress")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
}
}
else
{
rowData = sheet.GetRow(iRowIdx);
cellData = rowData.GetCell(0);
rowData = sheet.GetRow(iRowIdx);
if (rowData != null)
{
Supplier sup = new Supplier();
try
{
sup.CompanyId = 1;
cellData = rowData.GetCell(1);
col1 = cellData == null ? "" : cellData.ToString();
Supplier supp = context.Suppliers.Where(x => x.SUPPLIERCODES == col1.Trim()).FirstOrDefault();
if (supp == null)
{
sup.SUPPLIERCODES = col1.Trim();
cellData = rowData.GetCell(2);
col2 = cellData == null ? "" : cellData.ToString();
sup.Name = col2.Trim();
cellData = rowData.GetCell(3);
col3 = cellData == null ? "" : cellData.ToString();
sup.Brand = col3.Trim();
cellData = rowData.GetCell(4);
col4 = cellData == null ? "" : cellData.ToString();
sup.MobileNo = (col4.Trim());
cellData = rowData.GetCell(5);
col5 = cellData == null ? "" : cellData.ToString();
sup.OfficePhone = col5.Trim();
cellData = rowData.GetCell(6);
col6 = cellData == null ? "" : cellData.ToString();
sup.BillingAddress = col6.Trim();
cellData = rowData.GetCell(7);
col7 = cellData == null ? "" : cellData.ToString();
sup.TINNo = col7.Trim();
cellData = rowData.GetCell(8);
col8 = cellData == null ? "" : cellData.ToString();
sup.Bank_AC_No = col8.Trim();
cellData = rowData.GetCell(9);
col9 = cellData == null ? "" : cellData.ToString();
sup.Bank_Name = col9.Trim();
cellData = rowData.GetCell(10);
col10 = cellData == null ? "" : cellData.ToString();
sup.Bank_Ifsc = col10.Trim();
cellData = rowData.GetCell(11);
col11 = cellData == null ? "" : cellData.ToString();
sup.ShippingAddress = col11.Trim();
supCollection.Add(sup);
}
else
{
logger.Info("skcode Alredy Exist Skcode=:-" + col0);
}
}
catch (Exception ex)
{
logger.Error("Error adding customer in collection " + "\n\n" + ex.Message + "\n\n" + ex.InnerException + "\n\n" + ex.StackTrace + sup.Name);
}
}
}
}
context.AddBulkSupplier(supCollection);
string m = "save collection";
}
catch (Exception ex)
{
logger.Error("Error loading URL for " + Url + "\n\n" + ex.Message + "\n\n" + ex.InnerException + "\n\n" + ex.StackTrace);
}
}
var FileUrl = Path.Combine(HttpContext.Current.Server.MapPath("~/UploadedFiles"), httpPostedFile.FileName);
httpPostedFile.SaveAs(FileUrl);
}
}
if (msgitemname != null)
{
return(msgitemname);
}
msg = "Your Exel data is succesfully saved";
return(msg);
}
public void UploadFile()
{
if (HttpContext.Current.Request.Files.AllKeys.Any())
{
var identity = User.Identity as ClaimsIdentity;
int compid = 0, userid = 0;
// Access claims
foreach (Claim claim in identity.Claims)
{
if (claim.Type == "compid")
{
compid = int.Parse(claim.Value);
}
if (claim.Type == "userid")
{
userid = int.Parse(claim.Value);
}
}
// Get the uploaded image from the Files collection
System.Web.HttpPostedFile httpPostedFile = HttpContext.Current.Request.Files["file"];
if (httpPostedFile != null)
{
// Validate the uploaded image(optional)
byte[] buffer = new byte[httpPostedFile.ContentLength];
using (BinaryReader br = new BinaryReader(httpPostedFile.InputStream))
{
br.Read(buffer, 0, buffer.Length);
}
XSSFWorkbook hssfwb;
// XSSFWorkbook workbook1;
using (MemoryStream memStream = new MemoryStream())
{
BinaryFormatter binForm = new BinaryFormatter();
memStream.Write(buffer, 0, buffer.Length);
memStream.Seek(0, SeekOrigin.Begin);
hssfwb = new XSSFWorkbook(memStream);
string sSheetName = hssfwb.GetSheetName(0);
ISheet sheet = hssfwb.GetSheet(sSheetName);
AuthContext context = new AuthContext();
IRow rowData;
ICell cellData = null;
// public static bool ScanForText(Worksheet worksheet, object search,
// out int columnIndex, out int rowIndex)
//{
// string searchText = search.ToString().ToLower();
// for (int i = 1; i <= 100; i++)
// {
// for (rowIndex = 1, columnIndex = i; rowIndex <= i; rowIndex++, columnIndex--)
// if (worksheet.Cells[rowIndex, columnIndex].Value != null &&
// worksheet.Cells[rowIndex, columnIndex].Value.ToString().
// ToLower() == searchText)
// return true;
// }
// columnIndex = -1; rowIndex = -1;
// return false;
//}
//public WorksheetReader(Excel.Worksheet worksheet)
//{
// _worksheet = worksheet;
// _headerColumns = valueValidators.Select
// (i => new HeaderTextColumn(i.HeaderText, i.Required)).ToArray();
// _valueValidators = valueValidators;
//}
//private bool ScanForHeaders(int rowIndex)
//{
// int counter = 0;
// for (var columnIndex = 1; columnIndex < 100; columnIndex++)
// {
// if (_worksheet.Cells[rowIndex, columnIndex].Value != null)
// {
// Excel.Range cell = _worksheet.Cells[rowIndex, columnIndex];
// string cellText = cell.Value.ToString().ToLower();
// var headerColumn = _headerColumns.FirstOrDefault
// (i => i.CellText.ToLower() == cellText);
// if (headerColumn != null)
// {
// headerColumn.SetColumn(cell);
// //No need to proceed further if found all titles
// if (_headerColumns.Count() == ++counter) break;
// }
// }
// }
// return _headerColumns.All(i => !i.Required || i.ColumnIndex > 0);
//}
//private string HeadersErrorMessage()
//{
// IEnumerable<string> names = _headerColumns.Where(
// i => i.Required && i.ColumnIndex == 0).Select(i => i.CellText);
// return string.Format(
// "The following required headers are missing from the header row:{0} {1}.",
// Environment.NewLine, string.Join(", ", names));
//}
try
{
for (int iRowIdx = 1; iRowIdx <= sheet.LastRowNum; iRowIdx++) // iRowIdx = 0; HeaderRow
{
rowData = sheet.GetRow(iRowIdx);
if (rowData != null)
{
WarehouseCategory whcategory = new WarehouseCategory();
whcategory.CompanyId = 1; // compid;
cellData = rowData.GetCell(12);
try
{
cellData = rowData.GetCell(0);
if (cellData != null)
{
whcategory.WhCategoryid = int.Parse(cellData.ToString());
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(1);
if (cellData != null)
{
whcategory.Warehouseid = int.Parse(cellData.ToString());
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(2);
if (cellData != null)
{
whcategory.WarehouseName = cellData == null ? "" : cellData.ToString();
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(3);
if (cellData != null)
{
whcategory.Stateid = int.Parse(cellData.ToString());
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(4);
if (cellData != null)
{
whcategory.State = cellData == null ? "" : cellData.ToString();
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(5);
if (cellData != null)
{
whcategory.Cityid = int.Parse(cellData.ToString());
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(6);
if (cellData != null)
{
whcategory.City = cellData == null ? "" : cellData.ToString();
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(7);
if (cellData != null)
{
whcategory.Categoryid = int.Parse(cellData.ToString());
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(8);
if (cellData != null)
{
whcategory.CategoryName = cellData == null ? "" : cellData.ToString();
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(9);
whcategory.IsVisible = bool.Parse(cellData.ToString());
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(10);
if (cellData != null)
{
whcategory.SortOrder = int.Parse(cellData.ToString());
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(11);
if (cellData != null)
{
whcategory.Discription = cellData == null ? "" : cellData.ToString();
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(13);
if (cellData != null)
{
whcategory.CreatedDate = DateTime.Parse(cellData.ToString());
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(14);
if (cellData != null)
{
whcategory.UpdatedDate = DateTime.Parse(cellData.ToString());
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(15);
if (cellData != null)
{
whcategory.CreatedBy = cellData == null ? "" : cellData.ToString();
}
}
catch (Exception ex) { }
try
{
cellData = rowData.GetCell(16);
if (cellData != null)
{
whcategory.UpdateBy = cellData == null ? "" : cellData.ToString();
}
}
catch (Exception ex) { }
context.Addwarehousecatxl(whcategory);
}
}
// _UpdateStatus = true;
}
catch (Exception ex)
{
// logger.Error("Error loading URL for " + URL + "\n\n" + ex.Message + "\n\n" + ex.InnerException + "\n\n" + ex.StackTrace);
}
}
var FileUrl = Path.Combine(HttpContext.Current.Server.MapPath("~/UploadedFiles"), httpPostedFile.FileName);
httpPostedFile.SaveAs(FileUrl);
}
}
}
public string UploadFile()
{
if (HttpContext.Current.Request.Files.AllKeys.Any())
{
logger.Info("start current stock Upload Exel File: ");
var identity = User.Identity as ClaimsIdentity;
int compid = 0, userid = 0;
// Access claims
foreach (Claim claim in identity.Claims)
{
if (claim.Type == "compid")
{
compid = int.Parse(claim.Value);
}
if (claim.Type == "userid")
{
userid = int.Parse(claim.Value);
}
}
// Get the uploaded image from the Files collection
System.Web.HttpPostedFile httpPostedFile = HttpContext.Current.Request.Files["file"];
if (httpPostedFile != null)
{
// Validate the uploaded image(optional)
byte[] buffer = new byte[httpPostedFile.ContentLength];
using (BinaryReader br = new BinaryReader(httpPostedFile.InputStream))
{
br.Read(buffer, 0, buffer.Length);
}
XSSFWorkbook hssfwb;
// XSSFWorkbook workbook1;
using (MemoryStream memStream = new MemoryStream())
{
BinaryFormatter binForm = new BinaryFormatter();
memStream.Write(buffer, 0, buffer.Length);
memStream.Seek(0, SeekOrigin.Begin);
hssfwb = new XSSFWorkbook(memStream);
string sSheetName = hssfwb.GetSheetName(0);
ISheet sheet = hssfwb.GetSheet(sSheetName);
AuthContext context = new AuthContext();
IRow rowData;
ICell cellData = null;
try
{
List <CurrentStock> currentstkcollection = new List <CurrentStock>();
for (int iRowIdx = 0; iRowIdx <= sheet.LastRowNum; iRowIdx++) // iRowIdx = 0; HeaderRow
{
if (iRowIdx == 0)
{
rowData = sheet.GetRow(iRowIdx);
if (rowData != null)
{
string field = string.Empty;
field = rowData.GetCell(0).ToString();
if (field != "ItemNumber")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(1).ToString();
if (field != "StockId")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(2).ToString();
if (field != "ItemName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(3).ToString();
if (field != "CurrentInventory")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(4).ToString();
if (field != "WarehouseName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
}
}
else
{
rowData = sheet.GetRow(iRowIdx);
cellData = rowData.GetCell(0);
rowData = sheet.GetRow(iRowIdx);
if (rowData != null)
{
CurrentStock currntstk = new CurrentStock();
try
{
int cstid;
cellData = rowData.GetCell(0);
col0 = cellData == null ? "" : cellData.ToString();
currntstk.ItemNumber = col0.Trim();
logger.Info("ItemNumber :" + currntstk.ItemNumber);
cellData = rowData.GetCell(1);
col1 = cellData == null ? "" : cellData.ToString();
if ((col1 == null) || (col1 == ""))
{
cstid = 0;
}
else
{
cstid = Convert.ToInt32(col1);
}
currntstk.StockId = cstid;
cellData = rowData.GetCell(2);
col2 = cellData == null ? "" : cellData.ToString();
currntstk.ItemName = col2.Trim();
cellData = rowData.GetCell(3);
col3 = cellData == null ? "" : cellData.ToString();
currntstk.CurrentInventory = Convert.ToInt32(col3);
cellData = rowData.GetCell(4);
col4 = cellData == null ? "" : cellData.ToString();
currntstk.WarehouseName = col4.Trim();
currentstkcollection.Add(currntstk);
}
catch (Exception ex)
{
msgitemname = ex.Message;
logger.Error("Error adding customer in collection " + "\n\n" + ex.Message + "\n\n" + ex.InnerException + "\n\n" + ex.StackTrace + currntstk.ItemName);
}
}
}
}
context.Addcurrentstock(currentstkcollection);
string m = "save collection";
logger.Info(m);
}
catch (Exception ex)
{
logger.Error("Error loading for\n\n" + ex.Message + "\n\n" + ex.InnerException + "\n\n" + ex.StackTrace);
}
}
var FileUrl = Path.Combine(HttpContext.Current.Server.MapPath("~/UploadedFiles"), httpPostedFile.FileName);
httpPostedFile.SaveAs(FileUrl);
}
}
if (msgitemname != null)
{
return(msgitemname);
}
msg = "Your Exel data is succesfully saved";
return(msg);
}
public string UploadFile()
{
if (HttpContext.Current.Request.Files.AllKeys.Any())
{
logger.Info("start Item Upload Exel File: ");
var identity = User.Identity as ClaimsIdentity;
int compid = 1, userid = 0;
// Access claims
foreach (Claim claim in identity.Claims)
{
if (claim.Type == "compid")
{
compid = int.Parse(claim.Value);
}
if (claim.Type == "userid")
{
userid = int.Parse(claim.Value);
}
}
// Get the uploaded image from the Files collection
System.Web.HttpPostedFile httpPostedFile = HttpContext.Current.Request.Files["file"];
if (httpPostedFile != null)
{
// Validate the uploaded image(optional)
byte[] buffer = new byte[httpPostedFile.ContentLength];
using (BinaryReader br = new BinaryReader(httpPostedFile.InputStream))
{
br.Read(buffer, 0, buffer.Length);
}
XSSFWorkbook hssfwb;
using (MemoryStream memStream = new MemoryStream())
{
BinaryFormatter binForm = new BinaryFormatter();
memStream.Write(buffer, 0, buffer.Length);
memStream.Seek(0, SeekOrigin.Begin);
hssfwb = new XSSFWorkbook(memStream);
string sSheetName = hssfwb.GetSheetName(0);
ISheet sheet = hssfwb.GetSheet(sSheetName);
AuthContext context = new AuthContext();
IRow rowData;
ICell cellData = null;
try
{
List <ItemMaster> ItemCollection = new List <ItemMaster>();
List <Category> wcat = new List <Category>();
for (int iRowIdx = 0; iRowIdx <= sheet.LastRowNum; iRowIdx++)
{
if (iRowIdx == 0)
{
rowData = sheet.GetRow(iRowIdx);
if (rowData != null)
{
string field = string.Empty;
field = rowData.GetCell(0).ToString();
if (field != "CityName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(1).ToString();
if (field != "Cityid")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(2).ToString();
if (field != "CategoryName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(3).ToString();
if (field != "CategoryCode")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(4).ToString();
if (field != "SubcategoryName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(5).ToString();
if (field != "SubsubcategoryName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(6).ToString();
if (field != "BrandCode")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(7).ToString();
if (field != "itemname")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(8).ToString();
if (field != "itemcode")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(9).ToString();
if (field != "Number")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(10).ToString();
if (field != "SellingSku")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(11).ToString();
if (field != "price")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(12).ToString();
if (field != "PurchasePrice")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(13).ToString();
if (field != "UnitPrice")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(14).ToString();
if (field != "MinOrderQty")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(15).ToString();
if (field != "SellingUnitName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(16).ToString();
if (field != "PurchaseMinOrderQty")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(17).ToString();
if (field != "StoringItemName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(18).ToString();
if (field != "PurchaseSku")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(19).ToString();
if (field != "PurchaseUnitName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(20).ToString();
if (field != "SupplierName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(21).ToString();
if (field != "SUPPLIERCODES")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(22).ToString();
if (field != "BaseCategoryName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(23).ToString();
if (field != "TGrpName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(24).ToString();
if (field != "TotalTaxPercentage")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(25).ToString();
if (field != "WarehouseName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(26).ToString();
if (field != "HindiName")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(27).ToString();
if (field != "SizePerUnit")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(28).ToString();
if (field != "Barcode")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(29).ToString();
if (field != "Active")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(30).ToString();
if (field != "Deleted")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(31).ToString();
if (field != "Margin")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
field = rowData.GetCell(32).ToString();
if (field != "PromoPoint")
{
JavaScriptSerializer objJSSerializer = new JavaScriptSerializer(); strJSON = objJSSerializer.Serialize("Header Name " + field + " does not exist..try again");
return(strJSON);
}
}
}
else
{
rowData = sheet.GetRow(iRowIdx);
cellData = rowData.GetCell(0);
rowData = sheet.GetRow(iRowIdx);
if (rowData != null)
{
ItemMaster item = new ItemMaster();
try
{
item.CompanyId = 1;
Category cat = null;
SubCategory subcat = null;
SubsubCategory subsubcat = new SubsubCategory();
BaseCategory basecat = null;
//City Name -1
cellData = rowData.GetCell(0);
col0 = cellData == null ? "" : cellData.ToString();
if (col0.Trim() == "")
{
break;
}
item.CityName = col0.Trim();
//City Code -2
cellData = rowData.GetCell(1);
col1 = cellData == null ? "" : cellData.ToString();
string CityCode = col1.Trim();
City city = context.Cities.Where(x => x.CityName.Trim().ToLower() == item.CityName.Trim().ToLower()).SingleOrDefault();
if (city != null)
{
item.Cityid = city.Cityid;
}
//Warehouse Name 26
cellData = rowData.GetCell(25);
col25 = cellData == null ? "" : cellData.ToString();
item.WarehouseName = col25.Trim();
Warehouse w = context.Warehouses.Where(x => x.WarehouseName.Trim().ToLower() == item.WarehouseName.Trim().ToLower()).SingleOrDefault();
if (w != null)
{
item.warehouse_id = w.Warehouseid;
}
//base Category Name - 23
cellData = rowData.GetCell(22);
col22 = cellData == null ? "" : cellData.ToString();
string basecategory = col22.Trim();
basecat = context.BaseCategoryDb.Where(x => x.BaseCategoryName.ToLower().Equals(basecategory.ToLower())).SingleOrDefault();
if (basecat == null && col22.Trim() != "")
{
basecat = new BaseCategory();
basecat.BaseCategoryName = basecategory;
basecat.CompanyId = item.CompanyId;
if (w != null)
{
basecat.Warehouseid = w.Warehouseid;
}
else
{
basecat.Warehouseid = 1;
}
basecat.IsActive = true;
basecat = context.AddBaseCategory(basecat);
}
item.BaseCategoryName = basecat.BaseCategoryName;
item.BaseCategoryid = basecat.BaseCategoryId;
//Category Name -3
cellData = rowData.GetCell(2);
col2 = cellData == null ? "" : cellData.ToString();
item.CategoryName = col2.Trim();
//Category Code -4
cellData = rowData.GetCell(3);
col3 = cellData == null ? "" : cellData.ToString();
string CategoryCode = col3.Trim();
cat = context.Categorys.Where(x => x.CategoryName.Trim().Equals(col2.Trim())).SingleOrDefault();
if (cat == null && col3.Trim() != "")
{
cat = new Category();
cat.CategoryName = col2.Trim();
cat.Code = CategoryCode;
cat.BaseCategoryId = basecat.BaseCategoryId;
cat.CompanyId = item.CompanyId;
if (w != null)
{
cat.Warehouseid = w.Warehouseid;
}
else
{
cat.Warehouseid = 0;
}
cat.CompanyId = compid;
cat.IsActive = true;
cat = context.AddCategory(cat);
var wc = wcat.Where(x => x.Categoryid == cat.Categoryid).FirstOrDefault();
if (wc == null)
{
wcat.Add(cat);
}
}
else
{
if (w != null)
{
cat.Warehouseid = w.Warehouseid;
}
else
{
cat.Warehouseid = 0;
}
cat.CompanyId = compid;
cat.IsActive = true;
var wc = wcat.Where(x => x.Categoryid == cat.Categoryid).FirstOrDefault();
if (wc == null)
{
wcat.Add(cat);
}
}
item.Categoryid = cat.Categoryid;
item.CategoryName = cat.CategoryName;
//SubCategory Name - 5
cellData = rowData.GetCell(4);
col4 = cellData == null ? "" : cellData.ToString();
item.SubcategoryName = col4.Trim();
subcat = context.SubCategorys.Where(x => x.SubcategoryName.Trim().ToLower().Equals(col4.Trim().ToLower()) && x.Categoryid.Equals(cat.Categoryid)).SingleOrDefault();
if (subcat == null && col4.Trim() != "")
{
subcat = new SubCategory();
subcat.CompanyId = compid;
subcat.SubcategoryName = col4;
subcat.CategoryName = cat.CategoryName;
subcat.Categoryid = cat.Categoryid;
subcat.IsActive = true;
subcat = context.AddSubCategory(subcat);
}
item.SubCategoryId = subcat.SubCategoryId;
item.SubcategoryName = subcat.SubcategoryName;
//Brand Name ie. SubSubCategory - 6
cellData = rowData.GetCell(5);
col5 = cellData == null ? "" : cellData.ToString();
item.SubsubcategoryName = col5.Trim();
//Brand Code -7
cellData = rowData.GetCell(6);
col6 = cellData == null ? "" : cellData.ToString();
string BrandCode = col6.Trim();
subsubcat = context.SubsubCategorys.Where(x => x.SubsubcategoryName.Trim().ToLower().Equals(col5.Trim().ToLower()) && x.Categoryid.Equals(x.Categoryid) && x.SubCategoryId.Equals(subcat.SubCategoryId)).FirstOrDefault();
if (subsubcat == null && col6.Trim() != "")
{
subsubcat = new SubsubCategory();
subsubcat.CompanyId = compid;
subsubcat.SubsubcategoryName = col5;
subsubcat.SubCategoryId = subcat.SubCategoryId;
subsubcat.Code = col6;
subsubcat.SubcategoryName = col4;
subsubcat.CategoryName = cat.CategoryName;
subsubcat.Categoryid = cat.Categoryid;
subsubcat.IsActive = true;
subsubcat = context.AddSubsubCat(subsubcat);
}
item.SubsubCategoryid = subsubcat.SubsubCategoryid;
item.SubsubcategoryName = subsubcat.SubsubcategoryName;
//Item Name - 8
cellData = rowData.GetCell(7);
col7 = cellData == null ? "" : cellData.ToString();
item.itemname = col7.Trim();
//Item Code - 9
cellData = rowData.GetCell(8);
col8 = cellData == null ? "" : cellData.ToString();
item.itemcode = col8.Trim();
//Item Number - 10
cellData = rowData.GetCell(9);
col9 = cellData == null ? "" : cellData.ToString();
if (col9.Trim() == "" || col9 == null || col9 == "null")
{
break;
}
item.Number = col9.Trim();
//selling Sku - 11
cellData = rowData.GetCell(10);
col10 = cellData == null ? "" : cellData.ToString();
if (col10.Trim() == "" || col10 == null || col10 == "null")
{
break;
}
item.SellingSku = col10.Trim();
//MRP -12
cellData = rowData.GetCell(11);
col11 = cellData == null ? "" : cellData.ToString();
try {
item.price = Convert.ToDouble(col11);
}
catch (Exception e) { }
// Purchase Price - 13
cellData = rowData.GetCell(12);
col12 = cellData == null ? "" : cellData.ToString();
try
{
item.PurchasePrice = Convert.ToDouble(col12);
}
catch (Exception e) { }
//Selling Price - 14
cellData = rowData.GetCell(13);
col13 = cellData == null ? "" : cellData.ToString();
try
{
item.UnitPrice = Convert.ToDouble(col13);
}
catch (Exception e) { }
//selling MOQ -15
cellData = rowData.GetCell(14);
col14 = cellData == null ? "" : cellData.ToString();
try
{
item.MinOrderQty = Convert.ToInt32(col14);
}
catch (Exception e) { }
//selling Unit -16
cellData = rowData.GetCell(15);
col15 = cellData == null ? "" : cellData.ToString();
item.SellingUnitName = col15.Trim();
//ConversionFactor ,purchase qty -17
cellData = rowData.GetCell(16);
col16 = cellData == null ? "" : cellData.ToString();
try
{
item.PurchaseMinOrderQty = int.Parse(col16);
}
catch (Exception e) { }
//StoringItemName -18
cellData = rowData.GetCell(17);
col17 = cellData == null ? "" : cellData.ToString();
item.StoringItemName = col17.Trim();
//purchase sku -19
cellData = rowData.GetCell(18);
col18 = cellData == null ? "" : cellData.ToString();
if (col18.Trim() == "" || col18 == null || col18 == "null")
{
break;
}
item.PurchaseSku = col18.Trim();
//Purchase Unit -20
cellData = rowData.GetCell(19);
col19 = cellData == null ? "" : cellData.ToString();
item.PurchaseUnitName = col19.Trim();
//Supplier -21
cellData = rowData.GetCell(20);
col20 = cellData == null ? "" : cellData.ToString();
string spname = col20.Trim();
//Supplier code -22
cellData = rowData.GetCell(21);
col21 = cellData == null ? "" : cellData.ToString();
string sp_code = col21.Trim();
Supplier supplier = null;
supplier = context.Suppliers.Where(x => x.SUPPLIERCODES.Trim().Equals(sp_code.Trim())).SingleOrDefault();
if (supplier == null && col21.Trim() != "")
{
supplier = new Supplier();
supplier.Name = spname;
supplier.SUPPLIERCODES = sp_code;
supplier.CompanyId = compid;
supplier = context.AddSupplier(supplier);
}
item.SupplierId = supplier.SupplierId;
item.SupplierName = supplier.Name;
item.SUPPLIERCODES = supplier.SUPPLIERCODES;
// TaxGroup -24
cellData = rowData.GetCell(23);
col23 = cellData == null ? "" : cellData.ToString();
TaxGroup grp = context.DbTaxGroup.Where(x => x.TGrpName.Trim().ToLower().Equals(col23.Trim().ToLower())).SingleOrDefault();
item.TGrpName = col23.Trim();
if (grp != null)
{
item.GruopID = grp.GruopID;
}
// TotalTaxPercentage -25
cellData = rowData.GetCell(24);
col24 = cellData == null ? "" : cellData.ToString();
try
{
item.TotalTaxPercentage = double.Parse(col24);
}
catch (Exception e) { }
//item.active = true;
//hindi Nmae -27
cellData = rowData.GetCell(26);
col26 = cellData == null ? "" : cellData.ToString();
item.HindiName = col26.Trim();
//Sizeperunit Area-28
cellData = rowData.GetCell(27);
col27 = cellData == null ? "" : cellData.ToString();
try
{
item.SizePerUnit = double.Parse(col27);
}
catch (Exception e) {
item.SizePerUnit = 0;
}
//barcode -29
cellData = rowData.GetCell(28);
col28 = cellData == null ? "" : cellData.ToString();
item.Barcode = col28.Trim();
cellData = rowData.GetCell(29);
col29 = cellData == null ? "" : cellData.ToString();
if (col29.Trim().ToLower() == "true")
{
item.active = true;
}
else if (col29.Trim().ToLower() == "false")
{
item.active = false;
}
else
{
item.active = false;
}
cellData = rowData.GetCell(30);
col30 = cellData == null ? "" : cellData.ToString();
if (col30.Trim().ToLower() == "true")
{
item.Deleted = true;
}
else if (col30.Trim().ToLower() == "false")
{
item.Deleted = false;
}
else
{
item.Deleted = false;
}
cellData = rowData.GetCell(31);
col31 = cellData == null ? "" : cellData.ToString();
try
{
item.Margin = double.Parse(col31);
}
catch (Exception e)
{
item.Margin = 0;
}
cellData = rowData.GetCell(32);
col32 = cellData == null ? "" : cellData.ToString();
try
{
item.promoPoint = int.Parse(col32);
}
catch (Exception e)
{
item.promoPoint = 0;
}
//barcode -29
cellData = rowData.GetCell(33);
col33 = cellData == null ? "" : cellData.ToString();
item.HSNCode = col33.Trim();
ItemCollection.Add(item);
}
catch (Exception ex)
{
logger.Error("Error adding item in collection " + "\n\n" + ex.Message + "\n\n" + ex.InnerException + "\n\n" + ex.StackTrace + item.itemname);
}
}
}
}
List <WarehouseCategory> wc1 = new List <WarehouseCategory>();
foreach (Category c in wcat)
{
Warehouse w = context.Warehouses.Where(x => x.Warehouseid == c.Warehouseid).SingleOrDefault();
WarehouseCategory wc = new WarehouseCategory();
wc.Categoryid = c.Categoryid;
wc.CategoryName = c.CategoryName;
wc.Warehouseid = w.Warehouseid;
wc.WarehouseName = w.WarehouseName;
wc.Stateid = w.Stateid;
wc.Cityid = w.Cityid;
wc.IsVisible = true;
wc1.Add(wc);
}
context.AddWarehouseCategory(wc1, "");
context.AddBulkItemMaster(ItemCollection);
try
{
var ass = context.AllWarehouseCategory(compid).ToList();
foreach (var a in ass)
{
AngularJSAuthentication.API.Helper.refreshItemMaster(a.Warehouseid, a.Categoryid);
}
}
catch (Exception ex)
{
logger.Error("Error in update Cache " + ex.Message);
}
}
catch (Exception ex)
{
logger.Error("Error in update Cache " + ex.Message);
}
}
var FileUrl = Path.Combine(HttpContext.Current.Server.MapPath("~/UploadedFiles"), httpPostedFile.FileName);
httpPostedFile.SaveAs(FileUrl);
}
}
if (msgitemname != null)
{
return(msgitemname);
}
msg = "Your Exel data is succesfully saved";
return(msg);
}
private async void Grindbtn_ClickAsync(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(selectedPath))
{
MessageBox.Show("Папка с файлами не выбрана");
return;
}
if (files.Length == 0)
{
MessageBox.Show("Папка с файлами пуста");
return;
}
if (surnameDT.Rows.Count == 0)
{
MessageBox.Show("Файл с фамилиями не выбран или пуст");
return;
}
Model.ClearDestinationWb();
List <SurnameObject> surnames = await Task.Run(() => Model.ScanBooks(files));
foreach (DataRow row in surnameDT.Rows)
{
var name = row[2].ToString().Trim();
if (string.IsNullOrEmpty(name))
{
continue;
}
if (name.Contains(value: "Цикловая") ||
name.Contains(value: "Студклуб") ||
name.Contains(value: "Библиотека") ||
name.Contains(value: "Бухгалтерия") ||
name.Contains(value: "Отдел кадров") ||
name.Contains(value: "Профком") ||
name.Contains(value: "А Г Р- специалисты"))
{
continue;
}
bool isFound = false;
if (CancelAction)
{
CancelAction = false;
break;
}
//TESTING STUB!!!
/*if (name.Trim() == "Бібікова Оксана Юріївна")
* {
* break;
* } */
// Search in Surnames List
var surname = surnames.Where(s => s.Surname == name).FirstOrDefault();
if (surname != null)
{
ShowInfo(this, "Копирую: " + name.Trim() + " в файле: " + surname.File.Trim());
isFound = true;
using (var fs = File.OpenRead(surname.File))
{
XSSFWorkbook workBook = new XSSFWorkbook(fs);
ISheet sheet = workBook.GetSheet(workBook.GetSheetName(0));
await Task.Run(() => Model.CopyPeople(workBook, sheet, surname));
}
}
if (!isFound)
{
AddNotFound(name);
}
}
// Create file and write WorkBook
Model.WriteOutputFile();
if (NotFoundSurnames.Count > 0)
{
Model.WriteNotFoundFile(NotFoundSurnames);
}
}
public override string GetWorksheetName()
{
return(WorkBook.GetSheetName(SheetIndex));
}
public String GetSheetName(int sheetIndex)
{
return(_uBook.GetSheetName(sheetIndex));
}
/// <summary>
/// Excel转换成DataSet（.xlsx/.xls）
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="strMsg"></param>
/// <returns></returns>
public static DataSet NPOI_ExcelToDataSet(string filePath, out string strMsg)
{
strMsg = "";
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string fileType = Path.GetExtension(filePath).ToLower();
string fileName = Path.GetFileName(filePath).ToLower();
try
{
ISheet sheet = null;
int sheetNumber = 0;
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (fileType == ".xlsx")
{
// 2007版本
XSSFWorkbook workbook = new XSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out strMsg);
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
MessageBox.Show("Sheet数据获取失败，原因：" + strMsg);
}
}
}
}
else if (fileType == ".xls")
{
// 2003版本
HSSFWorkbook workbook = new HSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out strMsg);
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
MessageBox.Show("Sheet数据获取失败，原因：" + strMsg);
}
}
}
}
return(ds);
}
catch (Exception ex)
{
strMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 连接至数据库，需要填入服务器地址，端口名，数据库名，用户名，密码，当前读取文件名
/// </summary>
/// <param name="serverPath">服务器地址</param>
/// <param name="serverPort">端口名</param>
/// <param name="dataBaseName">数据库名</param>
/// <param name="userName">用户名</param>
/// <param name="passwordText">密码</param>
/// <param name="fileStream">当前正在读取的表格的文件流</param>
public void ConnectToSql(string serverPath, string serverPort, string dataBaseName, string userName, string passwordText, FileStream fileStream)
{
// 连接数据库字符串，包含服务器地址，端口名，数据库名，用户名，密码
string constructorString =
"server=" + serverPath + ";" +
"port=" + serverPort + ";" +
"database=" + dataBaseName + ";" +
"user="******";" +
"pwd=" + passwordText + ";";
// 重置统计时间变量
stopwatch.Restart();
// 单次耗时
double timer;
// 连接MySql数据库
MySqlConnection mySqlConnection = new MySqlConnection(constructorString);
// 读取创建Excel文件
XSSFWorkbook wookBook = new XSSFWorkbook(fileStream);
// 服务端表头所在行数，第2行
var serverTitleRow = 1;
// 服务端数据开始行数，第4行
var serverDataRow = 3;
// 服务端单元格类型行数，第3行
var serverCellTypeRow = 2;
// 获取Excel文件第一张sheet表格的表名
string sheetName = wookBook.GetSheetName(0);
// 获取Excel文件第一张sheet表格
ISheet sheet = wookBook.GetSheetAt(0);
// 判断表头是否存在异常
bool titleIsError = false;
// Excel表格文件名
string fileStreamName = fileStream.Name.Replace(textBoxExcelURL.Text + @"\", "");
if (sheetName.StartsWith("Sheet") || sheetName.StartsWith("sheet") || sheetName == "")
{
stopwatch.Stop();
timer = stopwatch.ElapsedMilliseconds * 0.001;
timeCount += timer;
listBox.Items.Add("Error:文件[" + fileStreamName + "]表格名称不符合规范，该表格无法导出Json，请修改");
listBox.Items.Add("耗时" + timer + "秒");
listBox.Items.Add("*********************************************");
}
else
{
//服务端表头
IRow titleRow = sheet.GetRow(serverTitleRow);
//单元格类型表头
IRow cellTypeRow = sheet.GetRow(serverCellTypeRow);
//最后一格的编号，即列数
int columnCount = titleRow.LastCellNum;
//遍历表头是否有空值，如果有则不导出
for (int i = titleRow.FirstCellNum; i < columnCount; i++)
{
if (titleRow.GetCell(i) == null || titleRow.GetCell(i).ToString().Length == 0)
{
stopwatch.Stop();
timer = stopwatch.ElapsedMilliseconds * 0.001;
timeCount += timer;
listBox.Items.Add("Error:文件[" + fileStreamName + "]表格，第" + (i + 1) + "列表头存在数据异常，该表格无法导出Json，请修改");
listBox.Items.Add("耗时" + timer + "秒");
listBox.Items.Add("*********************************************");
titleIsError = true;
break;
}
}
//遍历单元格类型表头是否有空值，如果有则不导出
for (int i = cellTypeRow.FirstCellNum; i < columnCount; i++)
{
if (cellTypeRow.GetCell(i) == null || cellTypeRow.GetCell(i).ToString().Length == 0)
{
stopwatch.Stop();
timer = stopwatch.ElapsedMilliseconds * 0.001;
timeCount += timer;
listBox.Items.Add("Error:文件[" + fileStreamName + "]表格，第" + (i + 1) + "列单元格类型存在数据异常，该表格无法导出Json，请修改");
listBox.Items.Add("耗时" + timer + "秒");
listBox.Items.Add("*********************************************");
titleIsError = true;
break;
}
}
try
{
// 判断表头是否存在异常
if (titleIsError == false)
{
// 打开通道，建立连接，可能出现异常,使用try catch语句
mySqlConnection.Open();
listBox.Items.Add("已经与服务器地址" + serverPath + "建立连接");
listBox.Items.Add("服务器用户" + userName);
// 最后一行的编号
int rowCount = sheet.LastRowNum;
string tableName = sheetName.ToLower() + "_table";
// 清空对应表格
string sql = "TRUNCATE TABLE " + tableName + ";";
// 表头名列表
List <string> columnsName = new List <string>();
// 建立DataTable
DataTable dataTable = mySqlConnection.GetSchema("Columns");
// 读取当前表对应的MySql表的表头
if (dataTable.Columns.Contains("COLUMN_NAME"))
{
foreach (DataRow dataRow in dataTable.Rows)
{
if (dataRow["TABLE_NAME"].ToString() == tableName)
{
columnsName.Add((string)dataRow["COLUMN_NAME"]);
}
}
}
// 需导出表头统计
string columnNameCountData = "";
string valueCountData = "";
// 遍历行
for (int i = serverDataRow; i <= rowCount; i++)
{
// 获取行
IRow row = sheet.GetRow(i);
// 判断该行是否为空
if (row == null)
{
listBox.Items.Add("Error:文件[" + fileStreamName + "]表格，第" + (i + 1) + "行存在数据异常");
break;
}
// 需要导出数据库表示
bool isToDataBaseDistinguish = false;
// 需导出表头统计
string columnNameCount = "";
// 需导出数据参数值统计
string valueCount = "";
// 遍历该行的列
for (int j = row.FirstCellNum; j < columnCount; j++)
{
if (titleRow.GetCell(j) != null && titleRow.GetCell(j).ToString().Length != 0)
{
// 表头名
string title = titleRow.GetCell(j).ToString().Trim();
// 遍历数据库表头
for (int x = 0; x <= columnsName.Count - 1; x++)
{
// 判断表头是否相同，数据是否需要导出
if (row.GetCell(j) != null && columnsName[x] == sheetName.ToLower() + title.ToLower())
{
// 目标单元格类型
CellType cellType = row.GetCell(j).CellType;
// 单元格类型表头
CellType rowCellType = default;
switch (cellTypeRow.GetCell(j).ToString().Trim())
{
case "int":
rowCellType = CellType.Numeric;
break;
case "string":
rowCellType = CellType.String;
break;
case "bool":
rowCellType = CellType.Boolean;
break;
case "":
rowCellType = CellType.Blank;
break;
}
// 判断数据是否异常
if (cellType != rowCellType && cellType != CellType.Formula)
{
stopwatch.Stop();
timer = stopwatch.ElapsedMilliseconds * 0.001;
timeCount += timer;
listBox.Items.Add("Error:文件[" + fileStreamName + "]表格，第" + (i + 1) + "行，第" + (j + 1) + "列单元格类型存在数据异常，该表格无法继续导出Json，请修改");
listBox.Items.Add("耗时" + timer + "秒");
listBox.Items.Add("*********************************************");
return;
}
else
{
// 判断单元格类型
switch (cellType)
{
case CellType.Numeric:
valueCount = valueCount + ", '" + row.GetCell(j).NumericCellValue.ToString().Trim() + "'";
break;
case CellType.String:
valueCount = valueCount + ", '" + row.GetCell(j).StringCellValue.ToString().Trim() + "'";
break;
case CellType.Formula:
switch (rowCellType)
{
case CellType.Numeric:
valueCount = valueCount + ", '" + row.GetCell(j).NumericCellValue.ToString().Trim() + "'";
break;
case CellType.String:
valueCount = valueCount + ", '" + row.GetCell(j).StringCellValue.ToString().Trim() + "'";
break;
case CellType.Boolean:
valueCount = valueCount + ", '" + row.GetCell(j).BooleanCellValue.ToString().Trim() + "'";
break;
}
break;
case CellType.Blank:
valueCount = valueCount + ", ''";
break;
case CellType.Boolean:
valueCount = valueCount + ", '" + row.GetCell(j).BooleanCellValue.ToString().Trim() + "'";
break;
case CellType.Unknown:
listBox.Items.Add("Error:文件[" + fileStreamName + "]表格，第" + (i + 1) + "行，第" + (j + 1) + "列单元格类型未知");
break;
case CellType.Error:
listBox.Items.Add("Error:文件[" + fileStreamName + "]表格，第" + (i + 1) + "行，第" + (j + 1) + "列单元格类型存在异常");
break;
}
columnNameCount = columnNameCount + ", " + columnsName[x];
isToDataBaseDistinguish = true;
}
}
}
}
}
//if (isToDataBaseDistinguish)
//{
// sql = sql + "INSERT INTO " + tableName + " (id" + columnNameCount + ") VALUES ('" + (i - serverDataRow) + "'" + valueCount + ");";
//}
if (isToDataBaseDistinguish)
{
columnNameCountData = "(id" + columnNameCount + ")";
valueCountData = valueCountData + "('" + (i - serverDataRow) + "'" + valueCount + "),";
//listBox.Items.Add("INSERT INTO " + tableName + " (id" + columnNameCount + ") VALUES ('" + (i - serverDataRow) + "'" + valueCount + ");");
}
}
valueCountData = valueCountData.Substring(0, valueCountData.Length - 1);
sql = sql + "INSERT INTO " + tableName + " " + columnNameCountData + " VALUES " + valueCountData + ";";
MySqlCommand mySqlCommand = new MySqlCommand(sql, mySqlConnection);
//MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(mySqlCommand);
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
//DataTable dataTable2 = new DataTable();
//mySqlDataAdapter.Fill(dataTable);
//计时
excelNumSuccessToMySqkDatabase += 1;
stopwatch.Stop();
timer = stopwatch.ElapsedMilliseconds * 0.001;
timeCount += timer;
listBox.Items.Add("文件[" + fileStreamName + "]导出至数据库成功");
listBox.Items.Add("耗时" + timer + "秒");
listBox.Items.Add("*********************************************");
}
}
catch (MySqlException ex)
{
listBox.Items.Add("Error:" + ex.Message);
listBox.Items.Add("*********************************************");
}
finally
{
// 关闭通道
mySqlConnection.Close();
}
}
}