class Program
{
public static LU_Excel.Workbook OpenBook(LU_Excel.Application excelInstance, string fileName, bool readOnly, bool editable, bool updateLinks)
{
LU_Excel.Workbook book = excelInstance.Workbooks.Open(
fileName, updateLinks, readOnly,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
return book;
}
public static void ReleaseRCM(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch
{
}
finally
{
o = null;
}
}
/*
* @param filePath absolute path
* @param sheetIndex which sheet you want to read
* @param rangeToRead read area in sheet, like A1:B3, mean A1 to B3 is 6 units.
*/
public static void ReadFromExcel(string filePath, int sheetIndex, object rangeToRead)
{
Console.WriteLine("fileName: {0}, sheetIndex = {1}", filePath, sheetIndex);
LU_Excel.Application excel = null;
LU_Excel.Workbook book = null;
LU_Excel.Worksheet sheet = null;
LU_Excel.Range range = null;
object obj = System.Reflection.Missing.Value;
try
{
excel = new LU_Excel.Application();
book = OpenBook(excel, filePath + ".xls", true, false, false);
if (book.Worksheets.Count < sheetIndex || sheetIndex < 0)
{
Console.WriteLine("Invalid sheet index.....Worksheets.Count={0}, sheetIndex={1}", book.Worksheets.Count, sheetIndex);
return;
}
sheet = book.Sheets[sheetIndex] as LU_Excel.Worksheet;
if (sheet != null)
range = sheet.get_Range(rangeToRead, System.Reflection.Missing.Value);
string A1 = String.Empty;
if (range != null)
{
Console.WriteLine("{0}, {1}",range.Cells.Rows.Count, range.Cells.Columns.Count);
for (int i = 1; i <= range.Cells.Rows.Count; ++i)
{
for (int j = 1; j <= range.Cells.Columns.Count; ++j)
{
if (range.Cells[i, j].Value == null)
{
Console.WriteLine("range.Cells[{0}, {1}] = null.....", i, j);
range.Cells[i, j].value = "null";
Console.WriteLine("range.Cells[{0}, {1}] = {2}................", i, j, range.Cells[i, j].Value);
continue;
}
Console.WriteLine("range.Cells[{0}, {1}] = {2}", i, j, range.Cells[i, j].Value);
}
}
Random rd = new Random();
int num = rd.Next();
book.SaveAs(filePath + num / 1000 + ".xls", obj, obj, obj, obj, obj,
LU_Excel.XlSaveAsAccessMode.xlNoChange, true, obj, obj, obj);
book.Close(false, obj, obj);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
excel.Quit();
if (book != null)
ReleaseRCM(book);
if (range != null)
ReleaseRCM(range);
}
excel.Quit();
if (book != null)
ReleaseRCM(book);
if (range != null)
ReleaseRCM(range);
}
public static void WriteToExcel(string file)
{
LU_Excel.Application excel = null;
LU_Excel.Workbook book = null;
LU_Excel.Worksheet sheet = null;
LU_Excel.Range range = null;
LU_Excel.Font font = null;
object obj = System.Reflection.Missing.Value;
excel = new LU_Excel.Application();
book = excel.Workbooks.Add(obj);
sheet = book.Worksheets.get_Item(1);
range = sheet.get_Range("A1", obj);
range.Value = "Last Name";
range = sheet.get_Range("B1", obj);
range.Value = "First Name";
range = sheet.get_Range("A2", obj);
range.Value = "Doe";
range = sheet.get_Range("B2", obj);
range.Value = "John";
range = sheet.get_Range("A1:B1", obj);
font = range.Font;
font.Bold = true;
book.SaveAs(file, obj, obj, obj, obj, obj,
LU_Excel.XlSaveAsAccessMode.xlNoChange, true, obj, obj, obj);
book.Close(false, obj, obj);
excel.Quit();
}
static void Main(string[] args)
{
Console.WriteLine("one number = {0}", Program.aaa(30));
string file = @"d:\TestSheet";
ReadFromExcel(file, 1, "A1:C6");
{
public static LU_Excel.Workbook OpenBook(LU_Excel.Application excelInstance, string fileName, bool readOnly, bool editable, bool updateLinks)
{
LU_Excel.Workbook book = excelInstance.Workbooks.Open(
fileName, updateLinks, readOnly,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, editable, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
return book;
}
public static void ReleaseRCM(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch
{
}
finally
{
o = null;
}
}
/*
* @param filePath absolute path
* @param sheetIndex which sheet you want to read
* @param rangeToRead read area in sheet, like A1:B3, mean A1 to B3 is 6 units.
*/
public static void ReadFromExcel(string filePath, int sheetIndex, object rangeToRead)
{
Console.WriteLine("fileName: {0}, sheetIndex = {1}", filePath, sheetIndex);
LU_Excel.Application excel = null;
LU_Excel.Workbook book = null;
LU_Excel.Worksheet sheet = null;
LU_Excel.Range range = null;
object obj = System.Reflection.Missing.Value;
try
{
excel = new LU_Excel.Application();
book = OpenBook(excel, filePath + ".xls", true, false, false);
if (book.Worksheets.Count < sheetIndex || sheetIndex < 0)
{
Console.WriteLine("Invalid sheet index.....Worksheets.Count={0}, sheetIndex={1}", book.Worksheets.Count, sheetIndex);
return;
}
sheet = book.Sheets[sheetIndex] as LU_Excel.Worksheet;
if (sheet != null)
range = sheet.get_Range(rangeToRead, System.Reflection.Missing.Value);
string A1 = String.Empty;
if (range != null)
{
Console.WriteLine("{0}, {1}",range.Cells.Rows.Count, range.Cells.Columns.Count);
for (int i = 1; i <= range.Cells.Rows.Count; ++i)
{
for (int j = 1; j <= range.Cells.Columns.Count; ++j)
{
if (range.Cells[i, j].Value == null)
{
Console.WriteLine("range.Cells[{0}, {1}] = null.....", i, j);
range.Cells[i, j].value = "null";
Console.WriteLine("range.Cells[{0}, {1}] = {2}................", i, j, range.Cells[i, j].Value);
continue;
}
Console.WriteLine("range.Cells[{0}, {1}] = {2}", i, j, range.Cells[i, j].Value);
}
}
Random rd = new Random();
int num = rd.Next();
book.SaveAs(filePath + num / 1000 + ".xls", obj, obj, obj, obj, obj,
LU_Excel.XlSaveAsAccessMode.xlNoChange, true, obj, obj, obj);
book.Close(false, obj, obj);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
excel.Quit();
if (book != null)
ReleaseRCM(book);
if (range != null)
ReleaseRCM(range);
}
excel.Quit();
if (book != null)
ReleaseRCM(book);
if (range != null)
ReleaseRCM(range);
}
public static void WriteToExcel(string file)
{
LU_Excel.Application excel = null;
LU_Excel.Workbook book = null;
LU_Excel.Worksheet sheet = null;
LU_Excel.Range range = null;
LU_Excel.Font font = null;
object obj = System.Reflection.Missing.Value;
excel = new LU_Excel.Application();
book = excel.Workbooks.Add(obj);
sheet = book.Worksheets.get_Item(1);
range = sheet.get_Range("A1", obj);
range.Value = "Last Name";
range = sheet.get_Range("B1", obj);
range.Value = "First Name";
range = sheet.get_Range("A2", obj);
range.Value = "Doe";
range = sheet.get_Range("B2", obj);
range.Value = "John";
range = sheet.get_Range("A1:B1", obj);
font = range.Font;
font.Bold = true;
book.SaveAs(file, obj, obj, obj, obj, obj,
LU_Excel.XlSaveAsAccessMode.xlNoChange, true, obj, obj, obj);
book.Close(false, obj, obj);
excel.Quit();
}
static void Main(string[] args)
{
Console.WriteLine("one number = {0}", Program.aaa(30));
string file = @"d:\TestSheet";
ReadFromExcel(file, 1, "A1:C6");
WriteToExcel(file + "aa");
}
}