public void CreateNew_AddNewSheet_WithoutCreate_Exception_Test()
{
using (var con = new ExcelAdapter("TestData\\The New File.xlsx", op => op.AutoCreateNewFile = false))
{
con.AddNewSheet();
}
}
public void GetCellIndex_ReturnNull()
{
Assert.IsNull(ExcelAdapter.GetCellIndex("AA"));
Assert.IsNull(ExcelAdapter.GetCellIndex("123"));
Assert.IsNull(ExcelAdapter.GetCellIndex(""));
Assert.IsNull(ExcelAdapter.GetCellIndex(null));
}
private void ButtonOpen_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
var dialog = new OpenFileDialog();
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
var extension = Path.GetExtension(dialog.FileName);
List <Excel> excels = null;
switch (extension)
{
case ".uad":
var stream = File.ReadAllText(dialog.FileName);
excels = JsonConvert.DeserializeObject <List <Excel> >(stream);
break;
case ".xlsx":
case ".xls":
var excel = new ExcelAdapter(dialog.FileName);
var table = excel.GetFirstTableName();
excels = Excel.Parse(DefaultProfessional, excel.QueryDatatable("SELECT * FROM [" + table + "]", "sheet1"));
break;
default:
MessageBox.Show("不能识别的文件");
break;
}
Tree.DataSource = excels;
}
}
private void ButtonSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
//ChooseFile file = new ChooseFile();
//file.ShowDialog();
//return;
var excels = this.Tree.DataSource as List <Excel>;
if (excels == null)
{
return;
}
var id = Guid.NewGuid();
string startup = Path.Combine(Application.StartupPath, "result");
string uad = Path.Combine(startup, id + ".uad");
string excel = Path.Combine(startup, id + ".xlsx");
var stream = JsonConvert.SerializeObject(excels);
File.Create(uad).Close();
File.WriteAllText(uad, stream);
var array = excels.Where(m => m.IsSubheading == false).ToArray();
foreach (var item in array)
{
item.Subheading = excels.Where(m => m.ParentID == item.ID).ToList();
}
ExcelAdapter.Save(array, excel);
System.Diagnostics.Process.Start("explorer.exe", startup);
}
public void GivenIHaveASVMMeetingReport()
{
_sample = @"Z:\Downloads\SPL\SabreExcelImport\Spec\Samples\out.xls";
_ea = new ExcelAdapter();
_ea.Load(_sample);
_dsReport = _ea.DataSource;
}
/// <summary>
/// 界面加载数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
ExcelAdapter excel = new ExcelAdapter();
string fileName = this.textBox2.Text.Trim();
dt = excel.GetExcelDataTable(fileName);
if (dt == null || dt.Rows.Count == 0)
{
MessageBox.Show("所选文件无数据！");
return;
}
for (int i = 0; i < dt.Columns.Count; i++)
{
dataGridView2.Columns.Add(i.ToString(), dt.Columns[i].Caption);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
dataGridView2.Rows.Add();
for (int j = 0; j < dt.Columns.Count; j++)
{
dataGridView2.Rows[i].Cells[j].Value = dt.Rows[i][j];
}
}
}
public void WhenTheMessageHasAnXlsAttachment()
{
ExcelAdapter ea = new ExcelAdapter();
ea.Load(@"Z:\Downloads\SPL\SabreExcelImport\ServiceSample\Samples\out.xls");
_dsReport = ea.DataSource;
}
public void GetCellIndex_Test()
{
var cellIndex = ExcelAdapter.GetCellIndex("");
Assert.IsNull(cellIndex);
cellIndex = ExcelAdapter.GetCellIndex("A1");
Assert.IsNotNull(cellIndex);
Assert.AreEqual(cellIndex.RowIndex, 0);
Assert.AreEqual(cellIndex.ColumnIndex, 0);
cellIndex = ExcelAdapter.GetCellIndex("B2");
Assert.IsNotNull(cellIndex);
Assert.AreEqual(cellIndex.RowIndex, 1);
Assert.AreEqual(cellIndex.ColumnIndex, 1);
cellIndex = ExcelAdapter.GetCellIndex("Z100");
Assert.IsNotNull(cellIndex);
Assert.AreEqual(cellIndex.RowIndex, 99);
Assert.AreEqual(cellIndex.ColumnIndex, 25);
cellIndex = ExcelAdapter.GetCellIndex("Z90");
Assert.IsNotNull(cellIndex);
Assert.AreEqual(cellIndex.RowIndex, 89);
Assert.AreEqual(cellIndex.ColumnIndex, 25);
}
public void GetNextPartId_WithoutCreate_ReturnNull_Test()
{
using (var con = new ExcelAdapter("TestData\\The New File.xlsx", op => op.AutoCreateNewFile = false))
{
var priObj = new PrivateObject(con);
Assert.IsNull(priObj.Invoke("GetNextPartId"));
}
}
public ExcelRowGetSetter(ExcelAdapter excelAdapter, Row row)
{
Guard.ArgumentIsNotNull(excelAdapter, nameof(excelAdapter));
Guard.ArgumentIsNotNull(row, nameof(row));
ExcelAdapter = excelAdapter;
Row = row;
}
public void InitializeDataSet()
{
string _path = @"Z:\Downloads\SPL\SabreExcelImport\Spec\Samples\out.xls";
ExcelAdapter ea = new ExcelAdapter();
ea.Load(_path);
_dsReport = ea.DataSource;
}
public void SheetInfo_IsHidden_Test()
{
using (var con = new ExcelAdapter("TestData\\DataBaseInfo.xlsx"))
{
Assert.IsTrue(con.SheetNames.First(s => s.Name == "HiddenSheet").IsHidden);
Assert.IsTrue(con.SheetNames.Any(s => !s.IsHidden));
}
}
public void WriteDataToExcelRow_WithNullData_ReturnNull_Test()
{
using (var con = new ExcelAdapter("TestData\\2015 Weekly Calendar.xlsx", op => op.AutoCreateNewFile = false)
)
{
var priObj = new PrivateObject(con);
Assert.IsNull(priObj.Invoke("WriteDataToExcelRow", (DataRow)null, 0));
}
}
public void ExcelHelper_NotAutoOpen_Test()
{
using (var con = new ExcelAdapter("TestData\\2015 Weekly Calendar.xlsx", op => op.AutoCreateNewFile = false)
)
{
Assert.IsNotNull(con.DocumentFile == "TestData\\2015 Weekly Calendar.xlsx");
Assert.IsNull(con.SpreadsheetDocument);
}
}
public void ExcelHelper_AutoOpen_Test()
{
using (var con = new ExcelAdapter("TestData\\2015 Weekly Calendar.xlsx"))
{
Assert.IsTrue(con.SheetNames.Length > 0);
Assert.IsNotNull(con.DocumentFile == "TestData\\2015 Weekly Calendar.xlsx");
Assert.IsNotNull(con.SpreadsheetDocument);
}
}
public ExcelSheetGetSetter(ExcelAdapter excelAdapter, string sheetName, SheetData sheetData)
{
Guard.ArgumentIsNotNull(excelAdapter, nameof(excelAdapter));
Guard.ArgumentIsNotNull(sheetData, nameof(sheetData));
Guard.ArgumentIsNotNull(sheetName, nameof(sheetName));
ExcelAdapter = excelAdapter;
SheetData = sheetData;
Name = sheetName;
}
public void WhenISubmitMyQuestion(string testCase)
{
IData data = new ExcelAdapter(new ExcelData());
var testData = data.GetTestData(testCase);
//var testData = DataGenerator.GetTestData(testCase);
new ShareWithBbcNewsPage().Form.FillForm(testData);
message.ErrorMessages = Wait.WaitForElementsToBeVisible(new ShareWithBbcNewsPage().ErrorMessages, 10);
}
public void ToDataTable_SheetName_Test()
{
using (var con = new ExcelAdapter("TestData\\2015 Weekly Calendar.xlsx"))
{
using (var tb = con["2015 Weekly Calendar"].ToDataTable(columnNamingType: ColumnNamingType.ExcelType))
{
//Validate Data
ValidateCalendarData(tb);
}
}
}
public void ToDataTable_Test()
{
using (var con = new ExcelAdapter("TestData\\2015 Weekly Calendar.xlsx"))
{
using (var table = con[0].ToDataTable(false, columnNamingType: ColumnNamingType.ExcelType))
{
//Validate Data
ValidateCalendarData(table);
}
}
}
protected void Export(object sender, EventArgs e)
{
DataTable dt_feedback = GetFeedback(true);
if (dt_feedback.Rows.Count > 0)
{
String template_filename = "Survey Feedback-Template.xlsx";
String new_filename = template_filename.Replace("-Template.xlsx", "")
+ " - " + DateTime.Now.ToString().Replace("/", "-").Replace(":", "-") + ".xlsx";
String folder_dir = AppDomain.CurrentDomain.BaseDirectory + @"Dashboard\SurveyFeedback\XL\";
File.Copy(folder_dir + template_filename, folder_dir + Util.SanitiseStringForFilename(new_filename), true); // copy template file
// Add sheet with data for each territory
SpreadsheetDocument ss = ExcelAdapter.OpenSpreadSheet(folder_dir + new_filename, 99);
if (ss != null)
{
ExcelAdapter.AddDataToWorkSheet(ss, "Survey Feedback", dt_feedback, true, true, true);
ExcelAdapter.CloseSpreadSheet(ss);
FileInfo file = new FileInfo(folder_dir + new_filename);
if (file.Exists)
{
try
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=\"" + file.Name + "\"");
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(file.FullName);
Response.Flush();
ApplicationInstance.CompleteRequest();
Util.WriteLogWithDetails("Survey Feedback exported.", "surveyfeedback_log");
}
catch
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
finally
{
file.Delete();
}
}
else
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
}
}
else
{
Util.PageMessage(this, "Nothing to export!");
}
}
static void Main(string[] args)
{
var excelPath = @"D:\temp\WisdomDrops.xlsx";
var adapter = new ExcelAdapter();
adapter.GetAll(excelPath).ToList()
.ForEach(drop => Console.WriteLine($"ID:{drop.Id?.ToString()?? "NULL"} Tip:{drop.Tip} Rating:{drop.Rating}"));
Console.ReadKey();
}
public void ToDataSet_Test()
{
using (var con = new ExcelAdapter("TestData\\2015 Weekly Calendar.xlsx"))
{
using (var tb = con.ReadData(false))
{
Assert.IsNotNull(tb);
Assert.IsTrue(tb.Tables.Count > 0);
//Validate Data
ValidateCalendarData(tb.Tables[0]);
}
}
}
public void ReadDataFromEmptyHeader_Test()
{
using (var con = new ExcelAdapter("TestData\\The Empty SheetData.xlsx", op => op.AutoCreateNewFile = true))
{
for (var i = 0; i < _data.Columns.Count; i++)
{
_data.Rows[0][i] = string.Empty;
}
var name = con.AddNewSheet(_data, true);
var dt = con[name].ToDataTable(true);
Assert.AreEqual(_data.Rows.Count - 1, dt.Rows.Count);
}
}
public void GetCellReference_Test()
{
var reference = ExcelAdapter.GetCellReference(new CellIndex(0, 0));
Assert.IsNotNull(reference);
Assert.AreEqual(reference, "A1");
reference = ExcelAdapter.GetCellReference(new CellIndex(1, 1));
Assert.IsNotNull(reference);
Assert.AreEqual(reference, "B2");
reference = ExcelAdapter.GetCellReference(new CellIndex(99, 25));
Assert.IsNotNull(reference);
Assert.AreEqual(reference, "Z100");
}
public string UpdateSheet_WithDataTable()
{
const string fileName = "TestData\\The Update Data File.xlsx";
using (var con = new ExcelAdapter(fileName))
{
var sheetName = con.SheetNames.First();
_data.TableName = sheetName.Name;
con.Update(_data);
var sheetData = con[sheetName.Name].ToDataTable();
Assert.IsNotNull(sheetData);
Assert.IsTrue(sheetData.Rows.Count == _data.Rows.Count);
Assert.IsTrue(sheetData.Columns.Count == _data.Columns.Count);
}
return(fileName);
}
public void CreateNew_Default_Test()
{
const string fileName = "TestData\\The New Default File.xlsx";
using (var con = new ExcelAdapter(fileName))
{
con.Create();
Assert.IsNotNull(con.SpreadsheetDocument);
con.Save();
}
Assert.IsTrue(File.Exists(fileName));
using (var con = new ExcelAdapter(fileName))
{
Assert.IsTrue(con.SheetNames.Length >= 3);
}
}
public void AddNewSheet_WithDataTable()
{
const string fileName = "TestData\\The New Data File.xlsx";
using (var con = new ExcelAdapter(fileName, op =>
{
op.AutoCreateNewFile = true;
op.OpenMode = OpenMode.Editable;
}))
{
var name = con.AddNewSheet(_data);
Assert.IsTrue(con.SheetNames.Length > 3);
var sheetData = con[name].ToDataTable(true);
Assert.IsNotNull(sheetData);
Assert.IsTrue(sheetData.Rows.Count == _data.Rows.Count);
Assert.IsTrue(sheetData.Columns.Count == _data.Columns.Count);
}
}
public void CreateNew_NoSheet_Test()
{
const string fileName = "TestData\\The New Non Sheets File.xlsx";
using (var con = new ExcelAdapter(fileName, op =>
{
op.AutoCreateNewFile = false;
op.AddDefaultSheets = false;
}))
{
con.Create();
Assert.IsNotNull(con.SpreadsheetDocument);
}
Assert.IsTrue(File.Exists(fileName));
using (var con = new ExcelAdapter(fileName))
{
Assert.AreEqual(con.SheetNames.Length, 0);
}
}
protected void ExportToExcel(DataTable dt_data)
{
Util.WriteLogWithDetails("Exporting company search results from " + dd_facet.SelectedItem.Text + ".", "search_log");
String dir = AppDomain.CurrentDomain.BaseDirectory + @"dashboard\search\xl\xltemplate.xlsx";
SpreadsheetDocument ss = ExcelAdapter.OpenSpreadSheet(dir, 99);
if (ss != null)
{
ExcelAdapter.AddDataToWorkSheet(ss, "Exported", dt_data, true, true, false);
ExcelAdapter.CloseSpreadSheet(ss);
FileInfo file = new FileInfo(dir);
if (file.Exists)
{
try
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=\"" + dd_facet.SelectedItem.Text + " Export "
+ "(" + DateTime.Now.ToString().Replace(" ", "-").Replace("/", "_").Replace(":", "_")
.Substring(0, (DateTime.Now.ToString().Length - 3)) + DateTime.Now.ToString("tt") + ").xls\"");
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(file.FullName);
Response.Flush();
ApplicationInstance.CompleteRequest();
}
catch
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
}
else
{
Util.PageMessage(this, "There was an error downloading the Excel file. Please try again.");
}
}
}
private bool xuatfilemain(string filePath, string keys)
{
bool result;
try {
DataTable table = new DataTable();
string[] array = this.getlistColumnByName(keys).Split(new char[]
{
','
});
for (int i = 0; i < array.Length; i++)
{
string item = array[i];
table.Columns.Add(item, typeof(string));
}
ExcelAdapter excel = new ExcelAdapter(filePath);
excel.CreateAndWrite(table, "Phone", 1);
result = true;
}
catch (Exception ex_66) {
result = false;
}
return(result);
}
