ResxWriter: Generating .resx files from an Excel spreadsheet

/*
 * Patrick Bounaix
 * www.L0g1c4L.com
 * 
 * See AssemblyInfo.cs 
 * for License Information
 * */

#region using
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Windows.Forms;

using System.Data.OleDb;
using System.Xml.Serialization;
using System.Resources;
#endregion

namespace L0g1c4L.ResxWriter.UI
{
	/// <summary>
	/// Manager class for application.
	/// </summary>
	public class ClsManager : IDisposable
	{		
		#region Private...
		#region Properties
		private DataTable m_table = new DataTable("ResxTable");		
		string xml = Application.UserAppDataPath + @"\ResxWriter.xml";
		#endregion	

		#region Method
		private string BuildCommand()
		{
			// build select statement //
			System.Text.StringBuilder sql = new System.Text.StringBuilder(256);
			
			sql.Append(@"SELECT " + "[" +  ClsMain.settings.ControlColumnName + "]" + @", ");

			foreach (ClsLanguage l in ClsMain.settings.LanguageCollection)
			{
				sql.Append("[" + l.LanguageColumnName + "]" +  @",");
			}

			// remove trailing , //
			sql.Remove(sql.Length-1, 1);
			
			sql.Append(@" FROM [" + ClsMain.settings.SheetName + @"]");

			return sql.ToString();
		}
		#endregion
		#endregion

		#region Public...
		#region Constructor
		/// <summary>
		/// Primary application controller.
		/// </summary>
		public ClsManager()
		{				
			this.m_table.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;			
			this.SetDefaultSettings();
		}			
		#endregion

		#region Methods
		#region SetDefaultSettings
		/// <summary>
		/// Set default values if not specified
		/// from previous settings.
		/// </summary>
		public void SetDefaultSettings()
		{
			ClsMain.settings.ControlColumnName = "ControlName";
			ClsMain.settings.SheetName = "Sheet1$";

			ClsLanguage l0 = new ClsLanguage("English", "en-US");
			ClsLanguage l1 = new ClsLanguage("French", "fr-FR");
		
			ClsMain.settings.LanguageCollection.Clear();
			ClsMain.settings.LanguageCollection.Add(l0);
			ClsMain.settings.LanguageCollection.Add(l1);
		}
		#endregion

		#region IDisposable Members
		/// <summary>
		/// Dispose method.
		/// </summary>
		public void Dispose()
		{
			if (this.m_table != null)
			{
				this.m_table.Dispose();				
			}
		}

		#endregion

		#region GetDataFromExcelFile
		/// <summary>
		/// Creates DataTable populated with 
		/// data from Excel file. 
		/// </summary>
		/// <param name="filePath">The path of the Excel Spreadsheet to use.</param>
		/// <returns>bool</returns>
		public bool GetDataFromExcelFile(string filePath)
		{
			try
			{
				string strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
					+ filePath
					+ @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
				OleDbConnection  dbConn = new OleDbConnection(strConnectionString);
				dbConn.Open();
				string fetch = BuildCommand();
				OleDbCommand  cmdSelect = new OleDbCommand(fetch, dbConn);
				OleDbDataAdapter dbAdapter = new OleDbDataAdapter();
				dbAdapter.SelectCommand  = cmdSelect;				
				dbAdapter.Fill(this.m_table);
				dbConn.Close();
				dbConn = null;
				return true;
			}
			catch(System.Data.OleDb.OleDbException ex)
			{
				FrmMessage m = new FrmMessage();
				m.SetMessageText("ResxWriter - Error Message", "Cannot Load Excel File!\n\n\nException:\n\n" 
					+ ex.ToString());
				m.ShowDialog();
				return false;
			}			
		}
		#endregion

		#region WriteResources
		/// <summary>
		/// Writes out resx files from Excel Spreadsheet.
		/// </summary>
		/// <param name="filePath">The path of the Excel Spreadsheet to use.</param>
		public void WriteResources(string filePath)
		{
			if (filePath.Length > 0)
			{

				// get columns //
				foreach (ClsLanguage l in ClsMain.settings.LanguageCollection)
				{					
					ResXResourceWriter rw = new ResXResourceWriter(
						filePath + "." + l.LanguageCulture + ".resx");					
								
					for (int i=0; i<this.m_table.Rows.Count; i++)
					{
						ClsMain.frmMain.WriteProgressBar.Visible = true;
						ClsMain.frmMain.WriteProgressBar.Maximum = m_table.Rows.Count;

						try
						{							
							string name = m_table.Rows[i][ClsMain.settings.ControlColumnName].ToString();							
							string valu = m_table.Rows[i][l.LanguageColumnName].ToString();
							rw.AddResource(name, valu);

							ClsMain.frmMain.WriteProgressBar.Increment(i);
						}
						catch (System.ArgumentException)
						{					
						}
					}

					rw.Generate();
					rw.Close();
				}								

				ClsMain.frmMain.WriteProgressBar.Visible = false;

				FrmMessage m = new FrmMessage();
				m.SetMessageText("ResxWriter - Output Results",
					"Files were written to: " + filePath);
				m.ShowDialog();
			}
		}
		#endregion				

		#region LoadApplicationSettings
		/// <summary>
		/// Loads FrmMain window size and location.
		/// </summary>
		public void LoadApplicationSettings()
		{
			try
			{
				XmlSerializer xs = new XmlSerializer(typeof(ClsSettings));
				using (StreamReader sr = new StreamReader(xml))
				{
					ClsMain.settings.LanguageCollection.Clear();
					ClsMain.settings = (ClsSettings)xs.Deserialize(sr);
					ClsMain.frmMain.Size = ClsMain.settings.FormSize;
					ClsMain.frmMain.Location = ClsMain.settings.FormLocation;						
				}
			}
			catch (InvalidOperationException)
			{
			}
			catch (FileNotFoundException)
			{
			}
		}
		#endregion

		#region SaveApplicationSettings
		/// <summary>
		/// Saves FrmMain window size and location to file.
		/// </summary>
		public void SaveApplicationSettings()
		{
			try
			{
				XmlSerializer xs = new XmlSerializer(typeof(ClsSettings));
				using (StreamWriter sw = new StreamWriter(xml, false))
				{
					ClsMain.settings.FormSize = ClsMain.frmMain.Size;
					ClsMain.settings.FormLocation = ClsMain.frmMain.Location;
					xs.Serialize(sw, ClsMain.settings);
				}
			}
			catch (InvalidOperationException)			
			{
			}
			catch (FileNotFoundException)
			{
			}			
		}
		#endregion
		#endregion
		#endregion	
	}
}